Page 1 of 1

Can this be done in a single query?

Posted: Mon Apr 09, 2007 10:01 am
by seodevhead
Hey guys...

I have a 'users' table that holds information when a user registers for my application. One of the columns in 'registration_date' which is a DATETIME column.

For a 'Users Statistics' section of the application, I would like to list how many people registered for EACH of the past 7 days.

So basically right now I have it working by just SELECT'ing the COUNT(user_id) from the 'users' table, but I have 7 queries for each of the 7 days.

This is what I have so far:

Today's Registration Count:

Code: Select all

SELECT COUNT(user_id) FROM users WHERE DATE(registration_date)=CURDATE()
Yesterday's Registration Count:

Code: Select all

SELECT COUNT(user_id) FROM users WHERE DATE(registration_date)=CURDATE()-1
Saturday's Registration Count:

Code: Select all

SELECT COUNT(user_id) FROM users WHERE DATE(registration_date)=CURDATE()-2
...and so on.

I think 7 seperate queries is a bit much for this (is it?)... does anyone know how I can efficiently consolidate all this down to one query? I am clueless as to how to go about this. Thanks for any help and guidance.

Posted: Mon Apr 09, 2007 10:05 am
by Weirdan

Code: Select all

select DATE(registration_date), COUNT(user_id) 
FROM users 
group by DATE(registration_date)
having to_days(now()) - to_days(registration_date) <= 7
order by registration_date

Posted: Mon Apr 09, 2007 10:17 am
by seodevhead
Hey Wierdan,

Thanks for the help with that pretty brilliant query. However, I am getting a SQL error when I am trying to use it exactly as above:

#1054 - Unknown column 'registration_date' in 'having clause'

The column 'registration_date' does exist in my table as a DATETIME column. Any idea why this could be happening? Thanks so much for your assistance.

Posted: Mon Apr 09, 2007 10:23 am
by Weirdan
then change 'HAVING' to 'WHERE' - it must work that way. (string with 'WHERE' must be placed before the GROUP BY clause)

Posted: Mon Apr 09, 2007 12:28 pm
by seodevhead
Thanks so much Wierdan... I got it working and it is much better than having multiple queries. Thanks! :)