Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Feb 2005
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Group by and order by using count

    Hi

    I am having real problems with a query. I would usually solve this by doing one query and then another query based on the first to do the final sort (probably cheating!). I know how to do this in coldfusion, but not php.

    People have told me it is possible to do what i am after but none of their suggestions have worked so far (on another forum).

    Basically, I am grouping on a username and then counting the number of referrals that user has. I want to then order by, descending, the number of referrals, so the person with the most referrals comes at the top.

    Query syntax tried so far is here:

    SELECT user_1.username, Count(user.refid) AS CountOfrefid
    FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
    GROUP BY user_1.username order by Count(user.refid);

    and

    SELECT user_1.username, Count(user.refid) AS CountOfrefid
    FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
    GROUP BY user_1.username order by CountOfrefid;

    and

    SELECT user_1.username, Count(user.refid) AS CountOfrefid
    FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
    GROUP BY user_1.username order by 2;

    and

    SELECT distinct(user_1.username), Count(user.refid) AS CountOfrefid
    FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
    GROUP BY user_1.username order by 2;

    Anyone any ideas what I am doing wrong?

    Thanks

    Jo

  • #2
    New Coder
    Join Date
    Feb 2005
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please ignore - I got it working in the end.

    I was testing it in MS Access, which was where the problem lay. When I put it back into php, it worked fine. Here is the correct syntax in case anyone else is having similar problems:

    SELECT user_1.username, Count(user.refid) AS CountOfrefid
    FROM user INNER JOIN user AS user_1 ON user.refid = user_1.userid
    GROUP BY user_1.username order by CountOfrefid desc;

  • #3
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you give me the answer as to how you solved this...I am working with MS Access 2007 and trying to do the same thing.

    SELECT COUNT(ID) AS IDCnt, ID
    INTO IDCnt
    FROM TestVar2
    GROUP BY ID
    ORDER BY IDCnt Desc

    Cheers,

    Dan

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    Access doesn't allow you to use computed field aliases in GROUP BY or ORDER BY.

    So:
    Code:
    SELECT COUNT(ID) AS IDCnt, ID
    INTO TableIDCnt
    FROM TestVar2
    GROUP BY ID
    ORDER BY COUNT(ID) Desc
    or
    Code:
    SELECT COUNT(ID) AS IDCnt, ID
    INTO TableIDCnt
    FROM TestVar2
    GROUP BY ID
    ORDER BY 1 Desc
    You don't have to name the table that you are SELECT-ing INTO "tableIDCnt". The name "IDCnt" is fine. I just wanted to emphasize the difference between the field IDCnt and the table of the same name.

    The last ORDER BY shows that you can use field *numbers*, where the fields are in the order as SELECTed, starting at 1.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •