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

1
votes

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

enter image description here

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?

2
sqloraclejoincountsubquery
Sample data and expected results, as formatted text rather than images, would be helpful. It looks like you just need to pivot, which is described in the documentation (assuming you're on a fairly recent version of Oracle) and there are many examples here. But are your monthly counts supposed to span years - so the total across multiple years - or are you meant to only look at the last 12 months, or the current year, or something else? – Alex Poole
All the years in consideration. – Emma

2 Answers

1
votes

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), () )
0
votes

So the JAN column should contain the counts for January of every year.

I will assume that in addition to the horizontal sum, you want a vertical sum.

First you need to aggregate at different levels, then PIVOT to get the data from different rows into different columns of the same row.

To aggregate at different levels, Oracle provides GROUP BY extensions such as CUBE:

select prod_code, prod_description,
  nvl(extract(month from sale_datetime),0) mon,
  count(*) cnt
from product join product_sale using(prod_code)
group by cube((prod_code, prod_description), extract(month from sale_datetime))

Now you can pivot:

select * from (
  select prod_code, prod_description,
    nvl(extract(month from sale_datetime),0) mon,
    count(*) cnt
  from product join product_sale using(prod_code)
  group by cube((prod_code, prod_description), extract(month from sale_datetime))
)
pivot(max(cnt) for mon in(
  0 sum,1 jan,2 feb,3 mar,4 apr,5 may,6 jun,7 jul,8 aug,9 sep,10 oct,11 nov,12 dec
))
order by prod_description;

PROD_CODE   PROD_DESCRIPTION   SUM    JAN   FEB   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC 
        1   1                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        2   2                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        3   3                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        4   4                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        5   5                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        6   6                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        7   7                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        8   8                  516     61    57    62    60    62    30    31    31    30    31    30    31 
        9   9                  516     61    57    62    60    62    30    31    31    30    31    30    31 
                              4644    549   513   558   540   558   270   279   279   270   279   270   279

This is a simplified solution, for example I don't label the vertical sum row at the bottom. For a complete solution and explanation of this technique, see https://stewashton.wordpress.com/2016/07/18/spreadsheet-like-totals-and-subtotals/