472,096 Members | 2,299 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,096 software developers and data experts.

count(*) and ORDER by the result of count

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?


Nov 10 '08 #1
1 3136
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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.