Everyday SQL (2)

SQL today

For each comm_id, there are several items.

For each item, there is a fulfillment method.

For each item, there is a row in the data.

comm_id	Item_name	Method
100	Socks	A
100	Book	A
100	pen	B
21	clothes	B

 

We want to get a count of each method for each comm_id. We need a table like this instead

comm_id	Method A	Method B
100	2	1
21	0	1

 

Method 1: use case when

Select comm_id,

Sum(case when Method= “A” then 1 else 0 end) as MethodA,

Sum(case when Method= “B” then 1 else 0 end) as MethodB,

from table

group by comm_id

 

Method 2: use self-join

with tempTable as

(

select id, method, count(item)

from table

group by id, method

)

 

With A as

(

Select * from tempTable

where method=”A”

)

 

With B as

(

Select * from tempTable

where method=”B”

)

 

Select * from A

FULL OUTER JOIN B

On A.comm_id=B.comm_id

 

Method 3: use pivot function in sql

with tempTable as

(

select id, method, count(item)

from table

group by id, method

)

Pivot

(count(method) for method in A, B, C)

As PivotTable