SQL Query to group by month and display individual month sum for a record

I’m trying to sum product codes with a particular description as total products sold in a month over the years.

The two table used are:

CREATE TABLE product (
    prod_code          NUMBER(3) NOT NULL,
    prod_description   VARCHAR2(75) NOT NULL,
    prod_points        NUMBER(2) NOT NULL
);

CREATE TABLE product_sale (
    sale_no         NUMBER(8) NOT NULL,
    sale_datetime   DATE NOT NULL,
    sale_location   VARCHAR2(100) NOT NULL,
    prod_code       NUMBER(3) NOT NULL,
    officer_id     NUMBER(8) NOT NULL,
);

The date format is in ‘DD-MON-YYYY HH12:MI PM’.

So far I’m able to formulate this:

select  
d.prod_description, 
extract(month from sale_datetime) as mon, 
count(d.prod_code) as count
from product d 
 join product_sale o on d.prod_code = o.prod_code
group by d.prod_description, extract(month from sale_datetime);
order by d.prod_code;

How do I separate the count as different month columns and get the sum of count in a separate column?

Answer

I would just use conditional aggregation:

select d.prod_description, count(*) as total,
       sum(case when extract(month from sale_datetime) = 1 then 1 else 0 end) as jan, 
       sum(case when extract(month from sale_datetime) = 2 then 1 else 0 end) as feb, 
       . . .
from product d join
     product_sale o 
     on d.prod_code = o.prod_code
group by d.prod_code, d.prod_description
order by d.prod_code;

Note that for the ordering, prod_code needs to be in the group by — or you need to use an aggregation function such as order by min(d.prod_code).

If you want a separate row for totals, then use grouping sets:

group by grouping sets ( (d.prod_code, d.prod_description), () )