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