Count row value as mysql column name

Question

I have a 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 this:

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 the 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, and then count different statusand each into columns, as indicated above.

Answer 1

try it

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

Demo Screenshot

Like Jack said he just works with it

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

Demo Screenshot

Answer 2

Try this job to 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

Answer 3

, status ,

Mysql

, -

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;