CODEWITHSUNDEEP
mysqlsql
Jimmy
Jimmy

Reputation: 105

Create custom column in SQL Query

I have a table like this:

ITEM    PICTURE    CATEGORY    STOCK
 1      Mickey     Shirt        3
 2      Mickey     Jacket       2
 3      Donald     Jacket       0
 4      Donald     Shirt        6
 5      Donald     Hoodie       1
 6      Mickey     Hoodie       3
 7      Goofy      Shirt        1
 8      Goofy      Hoodie       2
 9      Goofy      Jacket       3

The ITEM column is auto increment

I need an output like this:

PICTURE    HOODIE    JACKET    SHIRT
Donald       1         0         6
Goofy        2         3         1
Mickey       3         2         3

Any idea how I can produce that output? Thank you

Upvotes: 0

Views: 3964

Answers (1)

Ullas
Ullas

Reputation: 11556

You can use CASE expression.

Query

select PICTURE,
sum(case CATEGORY when 'Hoodie' then STOCK else 0 end) as `HOODIE`,
sum(case CATEGORY when 'Jacket' then STOCK else 0 end) as `JACKET`,
sum(case CATEGORY when 'Shirt' then STOCK else 0 end) as `SHIRT`
from your_table_name
group by PICTURE;

Upvotes: 2

Related Questions