CODEWITHSUNDEEP
sqlsql-server
irishninja002
irishninja002

Reputation: 45

Sum different items in two different columns

I'd like to have two columns that show the sum of different items purchased. For example

DB Name: Purchases

CustomerID Shirt
123 Red
123 Red
123 Red
123 Blue
123 Blue

The result I'd like to get is the following

CustomerID Red Blue
123 3 2

I am able to create one column but I am having difficulty creating two different columns.

Thank you.

Upvotes: 1

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Just use conditional aggregation. This is particularly simple in MySQL:

select customerid,
       sum(shirt = 'Red') as red,
       sum(shirt = 'Blue') as blue
from t
group by customerid;

Here is a db<>fiddle.

The above uses a MySQL shortcut. The general syntax uses case:

select customerid,
       sum(case when shirt = 'Red' then 1 else 0 end) as red,
       sum(case when shirt = 'Blue' then 1 else 0 end) as blue
from t
group by customerid;

Upvotes: 1

Titi
Titi

Reputation: 121

Maybe you should complete the sum statement with that :

select customerid,
   sum(case when shirt = 'Red' Then 1 Else 0 End) as red,
   sum(case when shirt = 'Blue' Then 1 Else 0 End) as blue
from t
group by customerid;

Upvotes: 0

Related Questions