How can I group the number of values into columns based on month in sql

Question

I have a data set like

Code Date 
123  21-Jan-2000 
234  23-Feb-1999 
123  19-Jan-2001

and I would like to query the table to display the data like this:

Code Total (All months) Jan Feb ... Dec 
123    2                 1   1
234    1                 0   1

I really have no idea how to start this. Any hints or help is greatly appreciated.

Answer 1

We can try a pivot query:

SELECT
    Code,
    COUNT(Date) AS "Total (All months)",
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 1  THEN 1 END) AS Jan,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 2  THEN 1 END) AS Feb,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 3  THEN 1 END) AS Mar,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 4  THEN 1 END) AS Apr,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 5  THEN 1 END) AS May,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 6  THEN 1 END) AS Jun,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 7  THEN 1 END) AS Jul,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 8  THEN 1 END) AS Aug,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 9  THEN 1 END) AS Sep,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 10 THEN 1 END) AS Oct,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 11 THEN 1 END) AS Nov,
    COUNT(CASE WHEN EXTRACT(MONTH FROM Date) = 12 THEN 1 END) AS Dec
FROM yourTable
GROUP BY
    Code
ORDER BY
    Code;

Note that the above explicit pivot syntax can often outperform the counterpart which uses the PIVOT operator.