469,326 Members | 1,616 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

count(*) and ORDER by the result of count

bilibytes
128 100+
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. date1 - john - best poker
  2. date1 - bob  - best poker
  3. date1 - pip  - best poker
  4. date1 - ron   - poker unite
  5. date1 - clo   - poker unite
  6. date1 - foo   - poker unite
  7. date1 - fos   - poker unite
  8. date1 - koh   - gold poker
  9. date2 - ron   - poker unite
  10. date2 - bob  - poker unite
  11. date3 - bob  - poker unite
  12. data3 - mar  - poker hacks
i would like to count the number o attendants for each event at a specific date.

Expand|Select|Wrap|Line Numbers
  1. SELECT count(*)
  2.         FROM attending 
  3.         WHERE attending.date = '$date'
  4.         ORDER by the most populated events 
i would like to get:
(for date1)
Expand|Select|Wrap|Line Numbers
  1. poker unite 4
  2. best poker 3
  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
Nov 10 '08 #1
1 3047
bilibytes
128 100+
Got the answer,

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

Expand|Select|Wrap|Line Numbers
  1. SELECT count(*) AS what_you_count ORDER by 'what_you_count'
AS creates like an alias to the result array
Nov 10 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by JaNE | last post: by
6 posts views Thread by Good Man | last post: by
2 posts views Thread by bsder | last post: by
2 posts views Thread by bhu | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.