SQL query - counting what is not there

Asked Nov 27 '15 at 21:59

Active Nov 27 '15 at 22:10

Viewed 31 times

I collect thermostat data every 5min. Now I would like to know how many hours per day the heater is on. I do this with this query:

$sql = "SELECT round((COUNT(*)*5)/60,1) as Counter, date(timestamp) as WeekDay, Heating  
FROM `rawdata` 
WHERE Heating = 1 
GROUP BY DAY(timestamp);";

Works fine and gives me back the hours of the day, IF it was on for that day. For my graphs that I build on this data however, it would be better if I got a row in return with date and the value 0. In other words, I would need a row returned for each day.

Any help would be appreciated.

edited Nov 27 '15 at 22:02

Russ

4,091
21
32

asked Nov 27 '15 at 21:59

Gabrie

4

I normally have a table of 'days' and left or right join on that. – Keith John Hutchison Nov 27 '15 at 22:02
1

What @csmu said. This type of table, with the intervals already filled in, is known as a "pivot" table. – Darwin von Corax Nov 27 '15 at 22:07

SQL query - counting what is not there

0 Answers0