How to have a query with columns needing different conditions in MySQL?

Question

For a game, I want to count the number of user sign-ups by hour (using MySQL.). Quite easy, something like that:

SELECT COUNT(*), DAY(date_user), HOUR(date_user) 
FROM users, 
GROUP BY DAY(date_user), HOUR(date_user)

After that, I want to only take in consideration users which have played the game at least one time. I have a second table with scores.

SELECT COUNT(*), DAY(date_user), HOUR(date_user) 
FROM users, scores 
WHERE users.id = scores.userid 
GROUP BY DAY(date_user), HOUR(date_user)

Great... Now, I want the two queries to result in one table, like:

Global signups | Signups of playing users | Day | Hour

I have not found a working query for this yet. Should I use unions? Or joins?

Answer 1

Here's one way:

select 
    day(date_user)
,   hour(date_user)
,   count(distinct u.id) as GlobalSignups
,   count(distinct s.userid) as SignupsOfPlayingUsers
from users u
left join scores s on u.id = s.userid
group by day(date_user), hour(date_user)

Counting distinct user id's gives the total number of users. When the left join fails, s.userid will be NULL, and NULLs are not counted by count(). So count(distinct s.userid) returns the number of users with signups.