Count of Invoices for Each Hour

clbardwe@gpi.com · #1 (**permalink**) 01-15-07, 14:25

This is actually a MySQL query, but I think it's generic enough that any SQL flavour should help.

I need to get the number of invoices per hour between 7am to 9pm and "the rest" (for each day of a week). I thought I knew what I was doing, but I'm getting the total transactions for the day placed in an hour's field (and not any particular hour, that I can tell)

Here's my query:

Code:

SELECT 
     DAYNAME(TransDt) As 'Day'
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN COUNT(InvNum) ELSE 0 END) as NumTrans7
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN COUNT(InvNum) ELSE 0 END) as NumTrans8
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 9 THEN COUNT(InvNum) ELSE 0 END) as NumTrans9
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 10 THEN COUNT(InvNum) ELSE 0 END) as NumTrans10
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 11 THEN COUNT(InvNum) ELSE 0 END) as NumTrans11
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 12 THEN COUNT(InvNum) ELSE 0 END) as NumTrans12
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 13 THEN COUNT(InvNum) ELSE 0 END) as NumTrans13
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 14 THEN COUNT(InvNum) ELSE 0 END) as NumTrans14
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 15 THEN COUNT(InvNum) ELSE 0 END) as NumTrans15
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 16 THEN COUNT(InvNum) ELSE 0 END) as NumTrans16
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 17 THEN COUNT(InvNum) ELSE 0 END) as NumTrans17
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 18 THEN COUNT(InvNum) ELSE 0 END) as NumTrans18
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 19 THEN COUNT(InvNum) ELSE 0 END) as NumTrans19
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 20 THEN COUNT(InvNum) ELSE 0 END) as NumTrans20
     ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 21 THEN COUNT(InvNum) ELSE 0 END) as NumTrans21
     ,(CASE WHEN (DATE_FORMAT(TransDt, '%H') < 7) OR (DATE_FORMAT(TransDt, '%H') > 21) THEN COUNT(InvNum) ELSE 0 END) as NumTransOther
     ,COUNT(InvNum) AS TotalTrans
FROM 
    tblTransactions
WHERE 
    (StoreNum = 123)
    and (TransDt >= '2006-12-04 01:00:00') 
    and (TransDt <= '2006-12-11 00:59:59')
group by 
     DAYNAME(TransDt)
ORDER BY
     TransDt;

What did I do wrong?

TIA

LKBrwn_DBA · #2 (**permalink**) 01-16-07, 08:51

Try SUM() instead of COUNT():

Code:

SELECT 
     DAYNAME(TransDt) As 'Day'
     ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN 1 ELSE 0 END) as NumTrans7
     ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN 1 ELSE 0 END) as NumTrans8
     ,...etc...

clbardwe@gpi.com · #3 (**permalink**) 01-17-07, 08:59

That was it. Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech