I'm having trouble with a query I need to get some data from my db.
I have a table with the following data:
id user_id game_id date
1 1 1 2015-08-19
2 1 2 2015-08-19
3 1 3 2015-08-19
4 2 1 2015-08-20
5 2 2 2015-08-20
6 1 1 2015-08-20
7 1 1 2015-08-20
8 3 1 2015-08-20
9 2 1 2015-08-21
10 1 1 2015-08-21
11 1 2 2015-08-21
12 2 1 2015-08-21
I need to get the total amount of users that "played" on a date range, i.e 2015-08-10 to 2015-08-22, grouped by date and user_id. For example, with this data given the result should be:
total_players date
1 2015-08-19
3 2015-08-20
2 2015-08-21
I've tried this the following query but it doesn't group correctly:
SELECT date, COUNT(*) AS total_players
FROM plays
WHERE date BETWEEN '$start_date' AND '$end_date'
GROUP BY date, user_id
Can anybody help me with the correct query? Thanks everyone in advance.
SELECT date, COUNT(DISTINCT user_id)
FROM plays
WHERE date BETWEEN '$start_date' AND '$end_date'
GROUP BY date
Tested locally on test database with your data
total_players date
1 2015-08-19
3 2015-08-20
2 2015-08-21
http://sqlfiddle.com/#!9/08957/3
SELECT `date`, COUNT(DISTINCT user_id) AS total_players
FROM plays
GROUP BY `date`
or
SELECT `date`, COUNT(DISTINCT user_id) AS total_players
FROM plays
WHERE date BETWEEN '$start_date' AND '$end_date'
GROUP BY `date`