H2 - sum per day

Question

I need help with creating a query.

Currently it looks like this

SELECT  
    COUNT(*) AS ALERTS,
    EXTRACT (YEAR FROM DATE) AS YEAR,
    EXTRACT (MONTH FROM DATE) AS MONTH,
    EXTRACT (DAY FROM DATE) AS DAY,
    HOST,
    ALERTLEVEL
FROM 
    ALERTS
WHERE
    EXTRACT (DAY FROM DATE) > EXTRACT (DAY FROM CURRENT_DATE) -7
    AND
    EXTRACT (MONTH FROM DATE) >= EXTRACT (MONTH FROM CURRENT_DATE) 
    AND
    HOST = 'HOST'
GROUP BY
    YEAR,MONTH,DAY,HOST,ALERTLEVEL
ORDER BY 
    YEAR ASC,MONTH ASC,DAY ASC, ALERTLEVEL ASC

Result:

ALERTS      YEAR    MONTH   DAY     HOST    ALERTLEVEL  
5           2013    9       5       HOST    1
64          2013    9       5       HOST    2
11          2013    9       5       HOST    3
15          2013    9       5       HOST    4
16          2013    9       8       HOST    2
2           2013    9       9       HOST    1

I would like to have 4 columns for alertlevel instead of one and sum the column alerts per day for each alertlevel.

Desired result

YEAR    MONTH   DAY HOST    ALERTLEVEL1 ALERTLEVEL2 ALERTLEVEL3 ALERTLEVEL4  
2013    9       5   HOST    5           64          11          15   
2013    9       8   HOST    0           16           0           0   
2013    9       9   HOST    2            0           0           0

Is this possible to do?

Many thanks for any help!!

score 4 · Answer 1 · answered Oct 02 '13 at 08:20

SELECT  
    COUNT(*) AS ALERTS,
    EXTRACT (YEAR FROM DATE) AS YEAR,
    EXTRACT (MONTH FROM DATE) AS MONTH,
    EXTRACT (DAY FROM DATE) AS DAY,
    HOST,
    sum(case when ALERTLEVEL = 1 then 1 else 0 end) as ALERTLEVEL1,
    sum(case when ALERTLEVEL = 2 then 1 else 0 end) as ALERTLEVEL2,
    sum(case when ALERTLEVEL = 3 then 1 else 0 end) as ALERTLEVEL3,
    sum(case when ALERTLEVEL = 4 then 1 else 0 end) as ALERTLEVEL4
FROM 
    ALERTS
WHERE
    EXTRACT (DAY FROM DATE) > EXTRACT (DAY FROM CURRENT_DATE) -7
    AND
    EXTRACT (MONTH FROM DATE) >= EXTRACT (MONTH FROM CURRENT_DATE) 
    AND
    HOST = 'HOST'
GROUP BY
    YEAR,MONTH,DAY,HOST
ORDER BY 
    YEAR ASC,MONTH ASC,DAY ASC

Very nice! Are there any ways of modifying this solution to work for N number of ALERTLEVEL(s) ? — user955732, Oct 21 '13 at 20:15

H2 - sum per day

1 Answers1