Retrieving individual records from a group

Question

I have two tables, one is a table of names with a category tag, and the other is a table of ratings for each name

ID Name Category 1 Dave 1 2 John 1 3 Lisa 2 4 Jim 2 

and the invoice table

 PersonID Score 1 50 2 100 3 75 4 50 4 75 

Then I would like the query to return something like

 Category TotalScore Names 1 150 Dave, John 2 200 Lisa, Jim 

Is it possible to do this with a single request?

I can get the totals with a request for the amount and group them into categories, but I don’t see a way to get the names at my discretion.

Thank you very much

Answer 1

You need to use group_concat:

 select Category, sum(Score) as TotalScore, group_concat(Name) as Names from categories join scores on scores.category = categories.category group by category 

Or even better:

 group_concat(DISTINCT Name ORDER BY Name ASC SEPARATOR ',') as names 

Answer 2

Just add group_concat(Name) as names to your sum request.

Answer 3

Here is a solution working for Postgres (which does not have the group_concat () function):

 select category, sum(score) as TotalScore, array(select id from perso where category=P.category order by id) as Names from perso P JOIN scores S ON S."PersonID" = P.id GROUP BY category; 

(I know this is a MySQL question, but, nevertheless, someone can do this, but he needs an answer for Postgres :))