SQL query to Group By and Aggregate results

Question

Given this sample data:

select 'Ada' name, 'Apple' fruit, 2 score union all
select 'Ada', 'Apple', 10 union all
select 'Ada', 'Banana', 8 union all
select 'Ada', 'Cherry', 8 union all
select 'Bob', 'Apple', 5

I want to come up with a query that sums up the data into

name | fruit         | score
-----+---------------+------
 Ada | Apple         | 10
 Ada | Banana,Cherry | 8
 Bob | Apple         | 5

i.e. to group by names and fruit combined if they are of the same score and the max score if they are of the same fruit

I can achieve this by

select name, string_agg(fruit, ','), score from (
    select name, fruit, max(score) score from (
        select 'Ada' name, 'Apple' fruit, 2 score union all
        select 'Ada', 'Apple', 10 union all
        select 'Ada', 'Banana', 8 union all
        select 'Ada', 'Cherry', 8 union all
        select 'Bob', 'Apple', 5
    ) data
    group by name, fruit
) data
group by name, score

Is there a simpler way to write the query in one single select instead of two?

"No" is an acceptable answer if you can explain why writing it with one select is not possible.

Answer 1

As far as I know, this can't be done in a single step, because you have to use two different groupings - one to get the maximum score per name and fruit, and one for the string_agg, which is based on the aggregation from the first query.

That being said, it can be written in a bit more readable way using a common table expression:

WITH cte AS
(
    SELECT name, fruit, max(score) As score 
    FROM (
        SELECT 'Ada' name, 'Apple' fruit, 2 score UNION ALL
        SELECT 'Ada' name, 'Apple' fruit, 2 score UNION ALL
        SELECT 'Ada', 'Apple', 10 UNION ALL
        SELECT 'Ada', 'Banana', 8 UNION ALL
        SELECT 'Ada', 'Cherry', 8 UNION ALL
        SELECT 'Bob', 'Apple', 5
    ) data
    GROUP BY name, fruit
)

SELECT name, STRING_AGG(fruit, ','), score 
FROM cte
GROUP BY name, score