Search code examples
sqlsqlitespiceworks

Conditional SUM not returning any value

Writing an SQL query to get a device count out of spiceworks. I need to get the device type, the model, how many are in use and how many are unassigned.

Currently anything thats unassigned is marked as decommissioned. So I have the following query 

  SELECT `device_type` AS "Device",
    `model` AS "Model",
    SUM (CASE WHEN `user_tag` NOT LIKE "%decommissioned%" THEN 1 ELSE 0 END) AS "Assigned",
    SUM (CASE WHEN `user_tag` LIKE "%decommissioned%" THEN 1 ELSE 0 END) AS "Avail."
  FROM `devices`
  WHERE `auto_tag` LIKE "%LA2%" OR `user_tag` LIKE "%LA2%" OR `location` LIKE "%LA2%"
  Group by `device_type`, `model`​

EG: if marked as decommissioned count as 1 toward available, otherwise count as 1 toward assigned.

My current problem is that the Second SUM opperation is returning no data. Not even a 0. Im sure its something simple that im missing.

Thanks in advance for the help.

Solution

  • It came down to SpiceWorks choking on the formatting of the query. the back ticks were removed from around column names, double quotes replaced with single quotes.

    Works as expected.

    Thank you all for your assistance.

    SELECT 
  device_type
  , model
  , COUNT(CASE WHEN user_tag NOT LIKE '%decommissioned%' THEN 1 ELSE NULL END) as 'Assigned'
  , COUNT(CASE WHEN user_tag LIKE '%decommissioned%' THEN 1 ELSE NULL END) as 'Avail'
  , COUNT(*) as 'TotalItems'
FROM devices
WHERE auto_tag LIKE '%LA2%' OR user_tag LIKE '%LA2%' OR location LIKE '%LA2%'
GROUP BY model, device_type
ORDER BY location DESC, device_type ASC​