Improve by How to get the number of occourences for each distinct Concat Mysql group value
I am trying to solve this answer in order to get a more improved result. 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, which 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 this conclusion to get something like this
name | yes | no | ney | total
------------------------------
mike |2 |1 |1 | 4
john |1 |1 |1 | 3
source to share
Under the assumption (confirmed in the comments) that there are only three states yes
, no
and ney
, the simplest can only be the count
conditional case
operator:
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
source to share
You don't need a subquery in select
. You can get a counter for each state and then aggregate it:
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;
source to share