Elhendriks Elhendriks - 1 month ago 7
SQL Question

SQL: Multiple select that differ by only one condition

I've got a beginner question. My SQL table looks like:

| Date | Type | Manufacturer |
2016/04/01 A X
2016/04/01 B Y
2016/04/02 B X
2016/05/07 A Z
... ... ...


My aim is to count the quantity of "Types" by manufacturers between two dates. I would like to get a result like following:

| Manufacturer | Quantity_TypeA | Quantity_TypeB |
X 1 1
Y 0 1
Z 1 0


My query looks like:

select Manufacturer as Manufacturer,
COUNT(*) as Quantity_TypeA
From MyTable
Where [Type] = 'A' and
Date between '20150101' and '20160930',
COUNT(*) as Quantity_TypeB
From MyTable
Where [Type] = 'B' and
Date between '20150101' and '20160930'
group by Manufacturer Order by Quantity_TypeA DESC


I have also tried to use functions like CASE on the Type and it didn't work. I am missing something but what?

Answer

Try this

select Manufacturer as Manufacturer, 
 SUM(case when [Type] = 'A' then 1 else 0 end) as Quantity_TypeA,
 SUM(case when [Type] = 'B' then 1 else 0 end) as Quantity_TypeB
 From MyTable
 Where 
 Date between '20150101' and '20160930'
group by Manufacturer