Count table value by hour using sproc

Question

In my table i have date column with default value TIMESTAMP on it. i want to count my record by every hour. i used sproc like this

    SELECT username, 


    COUNT(IF(HOUR(date)=8,1,NULL)  and (IF(docs != null))) AS '8:30  - 9:00', 
    COUNT(IF(HOUR(date)=9,1,NULL)  and (IF(docs != null))) AS '9:00 - 10:00',
    COUNT(IF(HOUR(date)=10,1,NULL) and (IF(docs != null))) AS '10:00 - 11:00',
    COUNT(IF(HOUR(date)=11,1,NULL) and (IF(docs != null))) AS '11:00 - 12:00',
    COUNT(IF(HOUR(date)=12,1,NULL) and (IF(docs != null))) AS '12:00 - 1:00',
    COUNT(IF(HOUR(date)=13,1,NULL) and (IF(docs != null))) AS '1:00 - 2:00',
    COUNT(IF(HOUR(date)=14,1,NULL) and (IF(docs != null))) AS '2:00 - 3:00',
    COUNT(IF(HOUR(date)=15,1,NULL) and (IF(docs != null))) AS '3:00 - 4:00',
    COUNT(IF(HOUR(date)=16,1,NULL) and (IF(docs != null))) AS '4:00 - 5:00',
    COUNT(disblid) 'Total'


FROM claimloans 
group by username;

Without

and (IF(docs != null))

part my sproc working perfectly. i don't want to count row if row doc values is null. how can i achieve this ?

this is my working sproc

SELECT username, 

    COUNT(IF(HOUR(date)=8,1,NULL))  AS '8:30  - 9:00', 
    COUNT(IF(HOUR(date)=9,1,NULL))  AS '9:00 - 10:00',
    COUNT(IF(HOUR(date)=10,1,NULL)) AS '10:00 - 11:00',
    COUNT(IF(HOUR(date)=11,1,NULL)) AS '11:00 - 12:00',
    COUNT(IF(HOUR(date)=12,1,NULL)) AS '12:00 - 1:00',
    COUNT(IF(HOUR(date)=13,1,NULL)) AS '1:00 - 2:00',
    COUNT(IF(HOUR(date)=14,1,NULL)) AS '2:00 - 3:00',
    COUNT(IF(HOUR(date)=15,1,NULL)) AS '3:00 - 4:00',
    COUNT(IF(HOUR(date)=16,1,NULL)) AS '4:00 - 5:00',
    COUNT(disblid) 'Total'


FROM claimloans 
group by username;

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

Nothing is ever equal to null (both x = null and x != null are always false), so you'll want to change docs != NULL to docs IS NOT NULL.

edited May 23 '17 at 12:32

Community

1
1

answered Feb 18 '15 at 17:25

Steve Howard

6,737
1
26
37

I changed it to " and docs IS NOT NULL " but it still count the null values :( – rafalefighter Feb 18 '15 at 17:30

score 0 · Answer 2 · answered Feb 19 '15 at 18:29

So, this expression:

COUNT(IF(HOUR(date)=8,1,NULL))

counts 1, a hard-coded non-null expression, when the date's hour is 8. If you replace the 1 with doc:

COUNT(IF(HOUR(date)=8,doc,NULL))

the function will count based additionally on the contents of doc. That is, it will count the row not merely when the hour is 8 but when doc is not null at the same time.

score 0 · Accepted Answer · answered Feb 20 '15 at 13:41

Thanks all who helped me. With your helps I made following sproc which do exactly what i want.

    DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `hourcounter`(IN datestamp DATE)
BEGIN
SELECT username, 

    COUNT(IF(HOUR(modifytime)=8,1,NULL))  AS '8:30  - 9:00', 
    COUNT(IF(HOUR(modifytime)=9,1,NULL))  AS '9:00 - 10:00',
    COUNT(IF(HOUR(modifytime)=10,1,NULL)) AS '10:00 - 11:00',
    COUNT(IF(HOUR(modifytime)=11,1,NULL)) AS '11:00 - 12:00',
    COUNT(IF(HOUR(modifytime)=12,1,NULL)) AS '12:00 - 1:00',
    COUNT(IF(HOUR(modifytime)=13,1,NULL)) AS '1:00 - 2:00',
    COUNT(IF(HOUR(modifytime)=14,1,NULL)) AS '2:00 - 3:00',
    COUNT(IF(HOUR(modifytime)=15,1,NULL)) AS '3:00 - 4:00',
    COUNT(IF(HOUR(modifytime)=16,1,NULL)) AS '4:00 - 5:00',
    COUNT(docs) 'Total'


FROM claimloans 
WHERE docs != '' and DATE(date) = datestamp 
group by username;

   END

Thanks for all who helped me. I learn lot from your answers !!

Count table value by hour using sproc

3 Answers3