Hafiz K Hafiz K - 2 months ago 12
MySQL Question

MySQL Merge rows into columns

I have done some research but still couldnt solve my issue here.

I have this table in next image link:
Table Sample

Im trying to write a query so that I can get something like this image link:
Sample I need

I did try write a query as below :



select ticket_pic, case when status = 'open' then count(tickets_id) end as open_ticket, case when status = 'close' then count(tickets_id) end as close_ticket from gt_tickets group by ticket_pic, status


But it return wrong data
Please advice. Any hint will be much appreciate.

Cheers

Answer
select ticket_pic,
    SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) as open_ticket,
    SUM(CASE WHEN status = 'close' THEN 1 ELSE 0 END) as close_ticket      
from gt_tickets
group by ticket_pic

OR

select ticket_pic,
    COUNT(CASE WHEN status = 'open' THEN 1 ELSE NULL END) as open_ticket,
    COUNT(CASE WHEN status = 'close' THEN 1 ELSE NULL END) as   close_ticket        
from gt_tickets
group by ticket_pic