How to display MYSQL COUNT horizontally

Question

Hi I am trying to display COUNT result in horizontal way but to no success. Is there any easyway to do this?

I have this table (created in excel for demo purposes)

Now I want to display the result like this.

Any advise will be greatly appreciated.

Answer 1

SELECT store_name,
       SUM(CASE WHEN status = 'hold' THEN 1 ELSE 0 END) AS hold_count,
       SUM(CASE WHEN status = 'ship' THEN 1 ELSE 0 END) AS ship_count,
       SUM(CASE WHEN status = 'return' THEN 1 ELSE 0 END) AS return_count
FROM table
group by store_name

Answer 2

Use if clause inside sum-aggregate.

select store_name,
  sum(if(status='hold', 1, 0)) as 'hold',
  sum(if(status='ship', 1, 0)) as 'ship',
  sum(if(status='return', 1, 0)) as 'return'
from mytable
group by store_name;