...

View Full Version : Group by and order by using count



jopassmore
02-17-2007, 06:35 PM
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

jopassmore
02-17-2007, 07:12 PM
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;

dbonin1599
01-06-2010, 02:07 PM
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. :eek:

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

Cheers,

Dan :)

Old Pedant
01-06-2010, 07:11 PM
Access doesn't allow you to use computed field aliases in GROUP BY or ORDER BY.

So:


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

or


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum