CODEWITHSUNDEEP
mysqlsql
Long Ranger
Long Ranger

Reputation: 6008

mysql shows column based on values

I have a question about querying data. When I want to get the specific colors(red and blue) of the selling items and its count from the following data

id  item    color
1   card    red
2   card    red
3   card    blue
4   card    blue
5   light   red
6   light   blue
7   light   yellow
8   cup red
9   cup red
10  cup blue

into this format

item    red blue
card    2   2
light   1   1
cup     2   0

I started from this.

select item ,color, count(*) from shops where color in ('red','blue') group by item , color

But when I tried to separate "red","blue" into 2 columns. I have no idea how to do it. I would appreciate if someone could give some keywords or direction for this problem.

Upvotes: 1

Views: 33

Answers (2)

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this:

SELECT item, 
    SUM(CASE WHEN color = 'red' THEN 1 ELSE 0 END) AS red,
    SUM(CASE WHEN color = 'blue' THEN 1 ELSE 0 END) AS blue
FROM table_name
GROUP BY item;

Upvotes: 3

Stefano Zanini
Stefano Zanini

Reputation: 5916

Try with two counts, with a case inside them:

select  item,
        count(case when color = 'red' then item end) as red,
        count(case when color = 'blue' then item end) as blue
from    shops
group by item

Upvotes: 0

Related Questions