MySQL Forums
Forum List  »  Newbie

Query Help
Posted by: Ravi Malghan
Date: August 01, 2016 05:42PM

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

Options: ReplyQuote


Subject
Written By
Posted
Query Help
August 01, 2016 05:42PM
August 03, 2016 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.