I need some help with a query. I want to count number of rows for Class column per week and also number of rows for that class per week with a HOST = NULL or empty string.
Assuming I have a table like this
Class | HOST | INSERT_TIME
############################
A | XYZ | 2016-07-01 10:00:00
A | NULL | 2016-07-01 11:00:00
A | | 2016-07-01 12:00:00
B | YUV | 2016-07-01 13:00:00
B | NULL | 2016-07-01 14:00:00
C | XYZ | 2016-07-01 15:00:00
C | NULL | 2016-07-01 16:00:00
C | | 2016-07-01 17:00:00
C | HIJ | 2016-07-01 18:00:00
I want to get a count of each Class for each week
CLASS |WEEK |HOST |VALID_HOST
A | 26 |3 | 1
B | 26 |1 | 1
C | 26 |4 | 2
So far I just have the first three columns.
select CLASS, DATE_FORMAT(INSERT_TIME, '%U') WEEK, count(*) from alarms
where INSERT_TIME > '2016-07-01 00:00:00'
group by DATE_FORMAT(INSERT_TIME, '%U'), CLASS
order by CLASS, DATE_FORMAT(INSERT_TIME, '%U') asc