Can this be done in a single query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Can this be done in a single query?

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

then change 'HAVING' to 'WHERE' - it must work that way. (string with 'WHERE' must be placed before the GROUP BY clause)
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

Thanks so much Wierdan... I got it working and it is much better than having multiple queries. Thanks! :)
Post Reply