getting individual records from a group by

Question

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

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

and the score table is

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

I would then like a query that returned something like

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

Is this possible to do with one query?

I can get the totals with a sum query and grouping by category but cannot see a way to get the names as I would like.

Many thanks

score 4 · Accepted Answer · answered Feb 09 '11 at 14:03

4

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

answered Feb 09 '11 at 14:03

Lordalcol

990
7
22

I have tried to add an order by at the end of the sql and now the name just returns "BLOB". Here is the sql, any idea why it fails now that I have the order by included. Thanks. SELECT group_concat(DISTINCT `tblnames`.`name`,' ',`tblnames`.`surname` SEPARATOR ' and ') AS 'nameOfPair', Sum(`tblpersonscores`.`score`) AS 'totalScore' FROM `tblnames` Inner Join `tblpersonscores` ON `tblpersonscores`.`personID` = `tblnames`.`id` GROUP BY `tblnames`.`category` ORDER BY 'totalScore' ASC – Dave Feb 09 '11 at 15:45
It's not so beautiful to see, but if you try: "select * from (SELECT group_concat(DISTINCT tblnames.name,' ',tblnames.surname SEPARATOR ' and ') AS 'nameOfPair', Sum(tblpersonscores.score) AS 'totalScore' FROM tblnames Inner Join tblpersonscores ON tblpersonscores.personID = tblnames.id GROUP BY tblnames.category) as a order by TotalScore ASC" What do you get? – Lordalcol Feb 09 '11 at 15:49
This does work, but I'm not entirely sure why!! Could you explain it a little please? – Dave Feb 09 '11 at 15:53
I am not sure too, but I guess it orders the rows before grouping them. In this case the group_concat returns BLOB. But I repeat, I am not sure about this. – Lordalcol Feb 09 '11 at 16:24
Thanks for your reply, and thanks for sorting the problem, much appreciated! – Dave Feb 09 '11 at 16:51

score 1 · Answer 2 · answered Feb 09 '11 at 14:04

1

Just add group_concat(Name) as names into your sum query.

answered Feb 09 '11 at 14:04

Martin Smith

438,706
87
741
845

score 1 · Answer 3 · answered Feb 09 '11 at 14:42

Here is a solution working for Postgres (which doesn't have a 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 was a MySQL question, but nonetheless someone might google it up but needs an answer for Postgres :) )

getting individual records from a group by

3 Answers3