Menu
Example -1 : Nested subqueries SELECT job_id,AVG(salary) FROM employees GROUP BY job_id HAVING AVG(salary)< (SELECT MAX(AVG(min_salary)) FROM jobs WHERE job_id IN (SELECT job_id FROM job_history WHERE department_id BETWEEN 50 AND 100) GROUP BY job_id);
Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query.
A subquery is known as the inner query, and the query that contains subquery is known as the outer query. The inner query executed first gives the result to the outer query, and then the main/outer query will be performed. MySQL allows us to use subquery anywhere, but it must be closed within parenthesis.
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 ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With