Can this be done in a single query?
Posted: Mon Apr 09, 2007 10:01 am
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:
Yesterday's Registration Count:
Saturday's Registration Count:
...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.
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()Code: Select all
SELECT COUNT(user_id) FROM users WHERE DATE(registration_date)=CURDATE()-1Code: Select all
SELECT COUNT(user_id) FROM users WHERE DATE(registration_date)=CURDATE()-2I 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.