MySQL Nested Select Query?

Question

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?

Solution

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 ;

Comments

[ypercubeᵀᴹ]

What is the datatype of column date?

[Admin]

datetime. T I's solution worked perfectly. :-)

[Admin]

I'm getting an error: [#1054 - Unknown column 'date' in 'field list'] Any ideas?