title: SQL query to Group By and Aggregate results
body: <p>Given this sample data:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I want to come up with a query that sums up the data into</p>
  
  <pre><code>name | fruit         | score
  -----+---------------+------
   Ada | Apple         | 10
   Ada | Banana,Cherry | 8
   Bob | Apple         | 5
  </code></pre>
  
  <p>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</p>
  
  <p>I can achieve this by</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Is there a simpler way to write the query in one single select instead of two?</p>
  
  <p>"No" is an acceptable answer if you can explain why writing it with one select is not possible.</p>