fbituco fbituco - 4 months ago 10
SQL Question

SQL COUNT(*) in one table including zero values

I have SQL table (With more columns, but it is not important)

CREATE TABLE `test_results` (
`id` int AUTO_INCREMENT,
`date_time` datetime,
`altpn` varchar(60),
`error1` int,
`error2` int,
`error3` int,
PRIMARY KEY (`id`)
);


Data can be like: id, 2016-06-16 14:26:02, 9513 400, 590, 0, 0.

And on this table Im running this SQL query:

SELECT date(test_results.date_time) AS date, test_results.altpn as PN, COUNT(*) AS count
FROM test_results
WHERE (test_results.error1 = 1000 OR test_results.error1 = 1001 OR test_results.error2 = 1000 OR test_results.error2 = 1001 OR test_results.error3 = 1000 OR test_results.error3 = 1001)
GROUP BY date(test_results.date_time), test_results.altpn
ORDER BY date(test_results.date_time), test_results.altpn


This works OK, but I also need to know dates, when
COUNT(*)
is zero. I saw few topics about
LEFT JOIN
, but I am not able to modify codes to work with this table.

Answer

When you use WHERE, you filter rows with other error codes out and they cannot be counted. Instead, select all rows as usual and LEFT JOIN to a table where zero-rows do not exist:

SELECT * 
FROM test_results t1
LEFT JOIN test_results t2 ON t2.id=t1.id
  AND 
  (t2.error1 = 1000 OR t2.error1 = 1001 OR t2.error2 = 1000 
  OR t2.error2 = 1001 OR t2.error3 = 1000 OR t2.error3 = 1001) 

As you see, the result set includes rows with errors of 1000/1001 and NULL values for other errors. Now, simply group and count them:

SELECT date(t1.date_time) AS date, t1.altpn as PN, COUNT(t2.id) AS count 
FROM test_results t1
LEFT JOIN test_results t2 ON t2.id=t1.id
  AND 
  (t2.error1 = 1000 OR t2.error1 = 1001 OR t2.error2 = 1000 
  OR t2.error2 = 1001 OR t2.error3 = 1000 OR t2.error3 = 1001) 
GROUP BY date(t1.date_time), t1.altpn 
ORDER BY date(t1.date_time), t1.altpn

http://sqlfiddle.com/#!9/9c6236/4