Ok, so I have the following query:
SELECT MIN(`date`), `player_name`
FROM `player_playtime`
GROUP BY `player_name`
I then need to use this result inside the following query:
SELECT DATE(`date`) , COUNT(DISTINCT `player_name`)
FROM `player_playtime /*Use previous query result here*/`
GROUP BY DATE( `date`) DESC LIMIT 60
How would I go about doing this?
You just need to write the first query as a subquery (derived table), inside parentheses, pick an alias for it (t below) and alias the columns as well.
The DISTINCT can also be safely removed as the internal GROUP BY makes it redundant:
SELECT DATE(`date`) AS `date` , COUNT(`player_name`) AS `player_count`
FROM (
SELECT MIN(`date`) AS `date`, `player_name`
FROM `player_playtime`
GROUP BY `player_name`
) AS t
GROUP BY DATE( `date`) DESC LIMIT 60 ;
Since the COUNT is now obvious that is only counting rows of the derived table, you can replace it with COUNT(*) and further simplify the query:
SELECT t.date , COUNT(*) AS player_count
FROM (
SELECT DATE(MIN(`date`)) AS date
FROM player_playtime
GROUP BY player_name
) AS t
GROUP BY t.date DESC LIMIT 60 ;
Source: Stackoverflow.com