1

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.

DBunting
  • 38
  • 6
  • 1
    There is no way that this logic could produce a value for the first `sum()` and `NULL` for the second. Is there any way you can reproduce this on SQL Fiddle? Are you sure this is the exact query you are running? – Gordon Linoff Dec 02 '14 at 22:26
  • I am sure. I took it out of the SpiceWorks SQL Report entry box where Ive been working on it. Ive been trying to get it into fiddle for the last hour but its proving to be less then cooperative. (never used fiddle before, im more then likely just doing it wrong) – DBunting Dec 02 '14 at 23:17
  • @DBunting here's a simple example of the fiddle : http://sqlfiddle.com/#!5/979ef/1; and as Gordon said, there's nothing wrong with that. If the problem still persists, try once again to put your environment on the fiddle. – Yohanes Khosiawan 许先汉 Dec 03 '14 at 00:33
  • The problem is there are 84 columns in this table (I didnt make it, I wouldnt have done such a horrid thing) I was trying to get something as close to the production schema as I could. Obviously there is something ino that table that is broken. Will look and report back – DBunting Dec 03 '14 at 01:07
  • @DBunting . . . Just to elaborate. If there is a row in the output, then there is at least one matching row for the `join` conditions. Both `sum(case . . .)` expressions have `then` and `else` clauses that are non-null. Hence, these expressions cannot return `NULL` values. The issue is not the data, it is the query. – Gordon Linoff Dec 03 '14 at 02:48

1 Answers1

0

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​
DBunting
  • 38
  • 6