How to show column name and nested aggregation result

Question

I want to do a nested aggregation where I can choose whether aggregate the max, min, or avg of a nested avg aggregation.

I want my result to look like this: (genre, MAX or MIN or AVG of nested AVG)

Current i have a nested aggregation query which only returns the Maximum average price of a genre, but i can't seem to also select the genre corresponding to that maximum.

Consider the table games(gameID, gameName, genre, price), disregard gameID and gameName.

The nested query i have right now:

SELECT MAX(avggenreprice) AS maxavg
FROM    (
    SELECT genre as subgenre, AVG(price) as avggenreprice
    FROM games
    GROUP BY 
        subgenre
) AS sub;

Example result of this query:

|maxavg|

| 368.22|

I want it to look like this

| genre | maxavg |

| Racer | 368.22 |

Ive tried :

SELECT sub.subgenre, MAX(avggenreprice) AS maxavg
FROM    (
    SELECT genre as subgenre, AVG(price) as avggenreprice
    FROM games
    GROUP BY 
        subgenre
    ) AS sub
;

But this just gives me error code 1140

In your outer select just add what you wanna select ... so just add that "genre" column and if you wanna select just first result from inner select, use top 1 — Veljko89, Jul 25 '17 at 09:56

Adikari Nadeesha · Answer 1 · 2017-07-25T13:49:35.953

3

select top 1  subgenre AS genre ,  AVG (price) As maxavg   from games  
group by  subgenre order  by AVG (price) desc

edited Jul 25 '17 at 13:49

answered Jul 25 '17 at 11:48

Adikari Nadeesha

322
3
12

1

Thank you for this code snippet, which may provide some immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its educational value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with similar, but not identical, questions. Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Jul 25 '17 at 14:00

Xingzhou Liu · Answer 2 · 2017-07-25T10:28:28.923

0

SELECT subgenre, avggenreprice AS maxavg
FROM    (
    SELECT genre as subgenre, AVG(price) as avggenreprice
    FROM games
    GROUP BY 
        subgenre
) AS sub 
ORDER BY avggenreprice DESC 
Limit 1;

The 1140 code arises when MySQL is not sure how to process an aggregate function, and requires an explicit GROUP BY clause. See (MySQL #1140 - Mixing of GROUP columns).

The simplest solution for your case is to replace the aggregate function max with sort DESC and limit 1 clauses.

edited Jul 25 '17 at 10:28

answered Jul 25 '17 at 10:04

Xingzhou Liu

1,507
8
12

This returns several rows, which is no different than just running the inner aggregation. This doesnt work :( – noobcoder Jul 25 '17 at 10:08
Thats because theres more than one genre. Are you looking for the subgenre with the max average price instead? – Xingzhou Liu Jul 25 '17 at 10:11
Sorry, didnt read closely, the edited code above should give you what you want. – Xingzhou Liu Jul 25 '17 at 10:15

score 0 · Answer 3 · answered Jul 25 '17 at 10:50

0

Just use ORDER BY and LIMIT. A subquery is not necessary:

SELECT genre as subgenre, AVG(price) as avggenreprice
FROM games
GROUP BY subgenre
ORDER BY avggenreprice DESC
LIMIT 1;

answered Jul 25 '17 at 10:50

Gordon Linoff

1,242,037
58
646
786

How to show column name and nested aggregation result

3 Answers3