1

I am trying to fiddle around this answer to get a more improved output. I have a table like this

name |status
-------------
mike |yes
mike |yes
mike |no
mike |ney
john |no
john |ney
john |yes

to output something like this

name |status           |total
------------------------------
mike |yes-2,no-1,ney-1 | 4
john |yes-1,no-1,ney-1 | 3

Someone suggested this answer that works great.

SELECT name, GROUP_CONCAT(totalPerStatus) AS status, 
       (SELECT COUNT(*) FROM mytable WHERE name = t.name) AS total
FROM (
  SELECT name,      
         CONCAT(status, '-', COUNT(*)) AS totalPerStatus            
  FROM mytable
  GROUP BY name, status ) t
GROUP BY name;

But I want to improve on this output to get something like this

name | yes | no | ney | total
------------------------------
mike |2    |1   |1    | 4
john |1    |1   |1    | 3
Mureinik
  • 297,002
  • 52
  • 306
  • 350
scylla
  • 124
  • 9

2 Answers2

1

Under the assumption (confirmed in the comments) that the only three statuses are yes, no and ney, the simplest thing may be to just count conditional case statements:

SELECT   name,
         COUNT(CASE status WHEN 'yes' THEN 1 ELSE NULL END) AS yes,
         COUNT(CASE status WHEN 'no' THEN 1 ELSE NULL END) AS no,
         COUNT(CASE status WHEN 'ney' THEN 1 ELSE NULL END) AS ney,
         COUNT(*) AS total
FROM     mytable
GROUP BY name
Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

You don't need the subquery in the select. You can get the count for each status and then aggregate that:

SELECT name, GROUP_CONCAT(totalPerStatus) AS status, 
       SUM(cnt) as Total
FROM (SELECT name,      
             CONCAT(status, '-', COUNT(*)) AS totalPerStatus,
             COUNT(*) as cnt            
      FROM mytable
      GROUP BY name, status
     ) t
GROUP BY name;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This returns same output as before. check it out. It doesnt return seperate column for the options and total. – scylla Jun 27 '15 at 15:49
  • @scylia . . . I read "to output something like this" and thought you were looking for this output as well. – Gordon Linoff Jun 27 '15 at 16:14
  • Gratitude. Twas just what i wanted to improve on. but the output of your suggestion was perfect – scylla Jun 27 '15 at 21:34