CODEWITHSUNDEEP
mysqlnull
Ben
Ben

Reputation: 163

Query return 1 row when no data present

I am running several instances of the same query. The below version should not be returning any data but I am getting 1 row with 'null' in each of the columns:

SELECT (CASE ScanName WHEN 'SYSTEM-HQ' THEN 'HQ
System' END) AS System, 
sum(CASE pspplMSSeverity WHEN 1 THEN 10 WHEN 2 THEN 9 WHEN 
3 THEN 6 WHEN 4 THEN 3 END) AS Score, 
(sum(CASE pspplMSSeverity WHEN 1 THEN 10 WHEN 2 THEN 9 
WHEN 3 THEN 6 WHEN 4 THEN 3 END)/COUNT(pspplMSSeverity)) AS 
Grade 
FROM missingpatches 
WHERE ScanName like '%SYSTEM-HQ%' 
ORDER BY LAST_UPDATE DESC LIMIT 1

How can I modify this query to ensure that I am only returning data when valid values exist?

Could this be due to the use of the Case and Sum within the primary SQL statement that are causing the Null data to be returned?

Upvotes: 1

Views: 129

Answers (2)

somnath
somnath

Reputation: 1335

Most probably the table missingpatches contains 1 row that satisfy

WHERE ScanName like '%SYSTEM-HQ%'

i.e. a row with ScanName that contains 'SYSTEM-HQ' but not exactly equal to 'SYSTEM-HQ' which you are equating with in the select's 1st col.

In SQL, the columns are evaluated after tables are joined and where clauses are evaluated. Hence, the row you are seeing, is actually meeting the where clause criteria.

Upvotes: 0

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT (CASE ScanName WHEN 'SYSTEM-HQ' THEN 'HQ
System' END) AS System, 
sum(CASE pspplMSSeverity WHEN 1 THEN 10 WHEN 2 THEN 9 WHEN 
3 THEN 6 WHEN 4 THEN 3 END) AS Score, 
(sum(CASE pspplMSSeverity WHEN 1 THEN 10 WHEN 2 THEN 9 
WHEN 3 THEN 6 WHEN 4 THEN 3 END)/COUNT(pspplMSSeverity)) AS 
Grade 
FROM missingpatches 
WHERE ScanName like '%SYSTEM-HQ%' 
HAVING System IS NOT NULL # Added
ORDER BY LAST_UPDATE DESC LIMIT 1

Try add a HAVING.

Upvotes: 1

Related Questions