Search code examples
countgroup-concat

Count in group_concat

I have this situation in Mysql table.

-----------------
code gr. state
-----------------
10  a   available
10  a   sold
10  b   available
10  a   available
10  a   sold
10  a   printed
10  b   available
10  b   sold
10  b   available
------------------

I need to group these data for group getting something like

group a -> available(3), sold(2), printed(1)
group b -> available(2), sold(1), printed(0)

I tried combining group_concat() and count() but can't get the result I need.

My goal is to have 1 single row per group (group by is ok) The states are always these 3 (available, sold, printed)

thx for help

Solution

  • SUM with IF could give you the right answear.

    SELECT gr, 
sum(if(state,'available',1,0)) available, 
sum(if(state,'sold',1,0)) sold,
sum(if(state,'printed',1,0)) printed
FROM table
GROUP BY gr