SQL select count for more than one like

Question

I know how to get it to work if it is a one-count condition. How do I make it work for multiple conditions?

SELECT count(TableName.DeviceName where DeviceName like 'AR%' ) as DEVICE_Type_A,
       count(TableName.DeviceName where DeviceName like 'R%' ) as DEVICE_Type_B,
       count(TableName.DeviceName where DeviceName like 'P%' ) as DEVICE_Type_C,
       count(TableName.DeviceName where DeviceName like 'AM%' ) as DEVICE_Type_D,
  FROM DB.TableName TableName
 WHERE TableName.DURATIONMIN > '180'

Answer 1

You should use case statements!

SELECT count(case when DeviceName like 'AR%' then 1 end) as DEVICE_Type_A,
       count(case when DeviceName like 'R%' then 1 end) as DEVICE_Type_B,
       count(case when DeviceName like 'P%' then 1 end) as DEVICE_Type_C,
       count(case when DeviceName like 'AM%' then 1 end) as DEVICE_Type_D
FROM DB.TableName TableName
WHERE TableName.DURATIONMIN > '180' 

I left the count in. Personally, I think "sum" is clearer:

SELECT sum(case when DeviceName like 'AR%' then 1 else 0 end) as DEVICE_Type_A,
       sum(case when DeviceName like 'R%' then 1 else 0 end) as DEVICE_Type_B,
       sum(case when DeviceName like 'P%' then 1 else 0 end) as DEVICE_Type_C,
       sum(case when DeviceName like 'AM%' then 1 else 0 end) as DEVICE_Type_D
FROM DB.TableName TableName
WHERE TableName.DURATIONMIN > '180' 

Answer 2

You should use subselects:

SELECT 
    (SELECT count(*) form TableName.DeviceName where DeviceName like 'AR%' ) as DEVICE_Type_A,
    (SELECT count(*) from TableName.DeviceName where DeviceName like 'R%' ) as DEVICE_Type_B,
    (SELECT count(*) from TableName.DeviceName where DeviceName like 'P%' ) as DEVICE_Type_C,
    (SELECT count(*) from TableName.DeviceName where DeviceName like 'AM%' ) as DEVICE_Type_D,
FROM DB.TableName TableName
WHERE TableName.DURATIONMIN > '180'