How to perform a conditional count?-CodePudding

I need some help with a very specific question (so i think) and I hope it's solvable.

I have a table which look like this:

cdts	vehicle	device
20211101	car1	deviceX
20211101	car1	deviceY
20211101	car1	deviceZ
20211101	car2	deviceX
20211101	car2	deviceY
20211101	car3	deviceX
20211101	car3	deviceY
20211102	car1	deviceX
20211102	car1	deviceY
20211102	car2	deviceZ
20211102	car2	deviceX
20211102	car2	deviceY
20211102	car3	deviceX
20211102	car3	deviceY

I need to get a list how often a car had 2 devices or 3 devices, so it should look like this:

vehicle	2 Devices	3 Devices
car1	1	1
car2	1	1
car3	2	0

I don't really know how to get this right. Do I need subqueries, can I do a count() with a where clause?

CodePudding user response：

select vehicle 
     , sum(case when devicecount = 2 then 1 else 0 end) [2Devices]
     , sum(case when devicecount = 3 then 1 else 0 end) [3Devices]
from ( 
    select vehicle, count(device) devicecount
    from table
    group by vehicle,cdts
) t group by vehicle