count row values as column name mysql

Question

I have table user_completed

CREATE TABLE IF NOT EXISTS `user_completed` (
  `rowId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `designer_id` int(10) unsigned NOT NULL,
  `status` varchar(54) DEFAULT NULL,
  PRIMARY KEY (`rowId`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


INSERT INTO `user_completed` (`rowId`, `designer_id`, `status`) VALUES
(1, 1, accept),
(2, 1, reject),
(3, 1, accept),
(4, 1, reject),
(5, 1, overtime),
(6, 2, accept)
(7, 2, accept)
(8, 3, accept)
(9, 2, reject);

Which look like:

rowId   designer_id    status 
1           1          accept
2           1          reject
3           1          accept
4           1          reject
5           1          overtime
6           2          accept
7           2          accept
8           3          accept
9           2          reject

I want to get result below:

designer_id   accept   overtime   reject
1             2        1          2
2             2        0          1
3             1        0          0

But I have no idea how to group designer_id then count distinct status and each into columns like above.

you can find lots of answers here... https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql — , Oct 31 '20 at 14:07

score 9 · Accepted Answer · edited May 23 '17 at 12:06

Try this

SELECT  designer_id,
    SUM(IF(status = 'accept',1,0)) as 'Accept',
    SUM(IF(status = 'reject',1,0)) as 'Reject',
    SUM(IF(status = 'overtime',1,0)) as 'Overtime'
FROM 
    user_completed
Group By designer_id

Fiddle Demo

As Jack said It's simply workig with this

SELECT  designer_id,
    SUM(status = 'accept') as 'Accept',
    SUM(status = 'reject') as 'Reject',
    SUM(status = 'overtime') as 'Overtime'
FROM 
    user_completed
Group By designer_id

Fiddle Demo

score 1 · Answer 2 · answered Mar 24 '14 at 05:28

Try this one it wil work

select designer_id, 
count(case status  when 'accept'then 1 else null end)as accept,
count(case status when 'reject'then 1 else null end)as reject,
count(case status when 'overtime'then 1 else null end)as overtime
from user_completed group by designer_id

score 0 · Answer 3 · edited May 23 '17 at 12:33

If you don't know how many distinct status you have then you can check this link for solution

Mysql query to dynamically convert rows to columns

e.g you can use something like below

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
   CONCAT(
     'SUM(IF(`status` = "', `status`, '",1,0)) AS ', `status`)
  ) INTO @sql
FROM user_completed;

SET @sql = CONCAT('SELECT  designer_id, ', @sql, ' 
              FROM    user_completed
              GROUP  BY  designer_id');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

count row values as column name mysql

3 Answers3