0

I have searched through what appear to be relevant questions but am still unable to solve my issue.

SELECT c.name AS 'Computer', COUNT(h.hotfixid) AS '# Missing',
CASE
    WHEN COUNT(h.hotfixid) ='0' THEN 'Fully Patched'
    WHEN COUNT(h.hotfixid) BETWEEN 1 AND 4 THEN '1 - 4 Missing'
    WHEN COUNT(h.hotfixid) BETWEEN 5 AND 12 THEN '5 - 12 Missing'
    WHEN COUNT(h.hotfixid) >12 THEN 'Attention > 12 Missing' 
    END AS 'Status', 
c.os AS 'Operating System'
FROM hotfix h
LEFT JOIN computers c ON h.computerid=c.computerid
LEFT JOIN clients cl ON c.clientid=cl.clientid
WHERE h.Installed='0' AND h.Approved='1' AND c.clientid = '229'
GROUP BY c.name

The problem is that the above will not return anything that has a 0 or no records. The GROUP BY breaks it.

This query returns my expected result, but when I add GROUP BY to it no results are returned at all.

    SELECT 
CASE 
    WHEN COUNT(hotfixid) ='0' THEN 'Fully Patched'
    WHEN COUNT(hotfixid) BETWEEN 1 AND 4 THEN '1 - 4 Missing'
    WHEN COUNT(hotfixid) BETWEEN 5 AND 12 THEN '5 - 12 Missing'
    WHEN COUNT(hotfixid) >12 THEN 'Attention > 12 Missing'
    END AS 'Status'
FROM hotfix
WHERE computerid = '8176' AND hotfix.`approved` = '1' AND hotfix.`installed` = '0'

This query returns a 0, so I know I'm getting 0 and not a 'NULL' 

SELECT computerid,
    COUNT(hotfixid) 
    FROM hotfix
    WHERE computerid = '8176' AND hotfix.`approved` = '1' AND hotfix.`installed` = '0'

Edit.

The hotfix table Info:

enter image description here

My expected output is to list by machines, their patching status, but the Fully Patched Status wont display:

MySQL Yog output

Thanks for any assistance.

Regards Peter.

PeterM
  • 23
  • 3
  • 1
    Consider providing sample data set and with proper table definitions and also add your desired result set – M Khalid Junaid Oct 02 '14 at 05:34
  • possible duplicate of [How to include "zero" / "0" results in COUNT aggregate?](http://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate) – Phil Oct 02 '14 at 05:54
  • I think that's to be expected since the Grouping is being done on a field from another table. Which table is the one that will always have data? – RMK Oct 02 '14 at 06:19
  • Thanks @Phil, I have had a look at that link you posted, and changed my 'driving' table to computers and also changed the count from h.hotfixid to c.name. No difference in the result at all. – PeterM Oct 02 '14 at 06:19
  • Hi @RMK thnaks, I'm not sure wha tyou mean by which table will always have data, all the tables used in the query always have data. Ahhh, I think I see what your getting at. I tried GROUP BY h.computerid as that's the same table that contains the count data and nothing changed in my results apart from a slight change in the order machines displayed in. – PeterM Oct 02 '14 at 06:46

1 Answers1

0

It makes more sense to have computers left join hotfix. And c.os is missing in group by.

SELECT c.name AS 'Computer', COUNT(h.hotfixid) AS '# Missing',
CASE
    WHEN COUNT(h.hotfixid) ='0' THEN 'Fully Patched'
    WHEN COUNT(h.hotfixid) BETWEEN 1 AND 4 THEN '1 - 4 Missing'
    WHEN COUNT(h.hotfixid) BETWEEN 5 AND 12 THEN '5 - 12 Missing'
    WHEN COUNT(h.hotfixid) >12 THEN 'Attention > 12 Missing' 
    END AS 'Status', 
c.os AS 'Operating System'
FROM computers c
LEFT OUTER JOIN hotfix h ON c.computerid=h.computerid AND h.Installed='0' AND h.Approved='1'
LEFT OUTER JOIN clients cl ON c.clientid=cl.clientid AND c.clientid = '229'
GROUP BY c.name, c.os
anhlc
  • 13,839
  • 4
  • 33
  • 40