count(*) and ORDER by the result of count

[bilibytes]

Hi everyone,

Ok I want here to make a query which will reuse the result of a count(*) function.


lets say my website does events at different places for the same date, and also at different dates:
this makes a structure like this:

event_id | attending_id | date 

there may be for the SAME date:
-multiple records with the same event_id and different attending_id
("different attendants to the same event for a specific date")
-multiple records with different event_id and different attending_id
("different attendants are going to different events the same date")

repeat these two possibilities for different dates

i could have:

date1 - john - best poker
date1 - bob  - best poker
date1 - pip  - best poker
date1 - ron   - poker unite
date1 - clo   - poker unite
date1 - foo   - poker unite
date1 - fos   - poker unite
date1 - koh   - gold poker
date2 - ron   - poker unite
date2 - bob  - poker unite
date3 - bob  - poker unite
data3 - mar  - poker hacks

i would like to count the number o attendants for each event at a specific date.

SELECT count(*)
        FROM attending 
        WHERE attending.date = '$date'
        ORDER by the most populated events 

i would like to get:
(for date1)

poker unite 4
best poker 3
gold poker 1

is there a way to perform this?


PD: in my real table the events are stored under id (numbers), so i should then get the names for each event from another table would that be possible in the same query?

thankyou

bilibytes

[bilibytes]

Got the answer,

when you try to ORDER by count(*), you should use AS like this:

SELECT count(*) AS what_you_count ORDER by 'what_you_count'

AS creates like an alias to the result array