CODEWITHSUNDEEP
mysql
peterpeterson
peterpeterson

Reputation: 1325

count inside group_concat not working as expected

I am trying to run a query that will count the number of count per row, and the number of people having some value (and Ideally the percentage beside) inside the rows using group_concat, the problem is that for some reason is not working as expected.

can I use group_contact(count(*))?

Upvotes: 0

Views: 77

Answers (1)

fancyPants
fancyPants

Reputation: 51878

What you want to do is not that easy. GROUP_CONCAT() doesn't work that way.

My advice, forget about GROUP_CONCAT(). A database is there to store and get you the data, not to format it nicely. That's done in presentation layer or application layer.

So instead of 

SELECT room, course, teacher, count(*), group_concat(country) 
FROM your_table 
GROUP BY room, course, teacher;

do this:

SELECT room, course, teacher, country, count(*)
FROM your_table 
GROUP BY room, course, teacher, country;

You will get something like this instead:

Room A  Course A    Teacher A  ES  2
Room A  Course A    Teacher A  MX  1
Room A  Course A    Teacher A  IT  1
Room A  Course A    Teacher A  JP  1
Room B ...
...

Then sum the counts on application level. Or you could do 

SELECT room, course, teacher, country, count(*)
FROM your_table 
GROUP BY room, course, teacher, country
WITH ROLLUP;

Then you will get this:

Room A  Course A    Teacher A  ES    2
Room A  Course A    Teacher A  MX    1
Room A  Course A    Teacher A  IT    1
Room A  Course A    Teacher A  JP    1
NULL    NULL        NULL       NULL  5
Room B ...
...

Upvotes: 1

Related Questions