I have this table in DB2:
DATE
----------
09/11/2021
06/10/2021
28/11/2021
17/11/2021
11/10/2021
24/11/2021
07/11/2021
30/11/2021
I want to count how many times a date appeared in the table and group it by year and month, and display it like this:
| YEAR | OCTOBER | NOVEMBER |
----------------------------
| 2021 | 2 | 6 |
CodePudding user response：
You may use a generic routine described at the link dynamic pivot SQL Query in Db2.
Use the following call to get the desired result set for your case:
CALL PIVOT
(
'SELECT YEAR (DATE) AS YEAR, TO_CHAR (DATE, ''MONTH'') AS MONTH FROM DATES'
, 'YEAR'
, 'MONTH'
, 'MONTH'
, 'count'
, 'SESSION.PIVOT'
, '-'
, ?, ?, ?
);
The result is:
| YEAR | NOVEMBER | OCTOBER |
|---|---|---|
| 2021 | 6 | 2 |
CodePudding user response：
As months are a known quantity you could use a sum of a case statement:
select year(datecol) as year
,sum(case when month(datecol) = 1 then 1 else 0 end) as jan
,sum(case when month(datecol) = 2 then 1 else 0 end) as feb
,sum(case when month(datecol) = 3 then 1 else 0 end) as mar
,sum(case when month(datecol) = 4 then 1 else 0 end) as apr
,sum(case when month(datecol) = 5 then 1 else 0 end) as may
,sum(case when month(datecol) = 6 then 1 else 0 end) as jun
,sum(case when month(datecol) = 7 then 1 else 0 end) as jul
,sum(case when month(datecol) = 8 then 1 else 0 end) as aug
,sum(case when month(datecol) = 9 then 1 else 0 end) as sep
,sum(case when month(datecol) = 10 then 1 else 0 end) as oct
,sum(case when month(datecol) = 11 then 1 else 0 end) as nov
,sum(case when month(datecol) = 12 then 1 else 0 end) as dec
from datetest
group by year(datecol)
order by 1;
That will give you output similar to this:
YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2018 0 0 0 0 0 0 0 0 0 0 3 0
2019 0 0 0 0 0 0 0 0 0 1 2 0
2020 0 0 0 0 0 0 0 0 0 1 1 0
2021 0 0 0 0 0 0 0 0 0 2 6 0