I am trying to show total by date ranges and grand total at the end.
drop table EP;
create table EP (style varchar2(1), color varchar2(2), order_date date, order_qty number(2));
insert into EP values ('A','a1',to_date('01/01/2023','mm/dd/yyyy'), 10);
insert into EP values ('A','a2',to_date('01/01/2023','mm/dd/yyyy'), 12);
insert into EP values ('A','a3',to_date('01/01/2023','mm/dd/yyyy'), 15);
insert into EP values ('B','b1',to_date('01/05/2023','mm/dd/yyyy'), 5);
insert into EP values ('A','a3',to_date('01/05/2023','mm/dd/yyyy'), 8);
insert into EP values ('C','c1',to_date('01/06/2023','mm/dd/yyyy'), 3);
insert into EP values ('H','a1',to_date('01/07/2023','mm/dd/yyyy'), 25);
insert into EP values ('A','b3',to_date('01/07/2023','mm/dd/yyyy'), 10);
insert into EP values ('B','b3',to_date('01/10/2023','mm/dd/yyyy'), 15);
select * from EP;
I've tried using ROLLUP, but can't get it to work as I am adding more columns. Is it possible to achieve the following result in on query?
Expected Result:
------------------
STYLE COLOR DATE QTY
A a1 1/1/2023 10
A a2 1/1/2023 12
A a3 1/1/2023 15
Total: 37
B b1 1/5/2023 5
A a3 1/5/2023 8
Total: 13
C c1 1/6/2023 3
Total: 3
H a1 1/7/2023 25
A b3 1/7/2023 10
Total: 35
B b3 1/10/2023 15
Total: 15
Grand Total: 103
With sample data you posted:
SQL> select case when color is null and order_date is not null then 'Total:'
2 when color is null and order_date is null then 'Grand Total:'
3 else max(style)
4 end style,
5 color, order_date, sum(order_qty) qty
6 from ep
7 group by rollup(order_date, color)
8 order by order_date;
STYLE COLOR ORDER_DATE QTY
------------ ---------- ---------- ----------
A a1 01/01/2023 10
A a2 12
A a3 15
Total: 37
A a3 05/01/2023 8
B b1 5
Total: 13
C c1 06/01/2023 3
Total: 3
H a1 07/01/2023 25
A b3 10
Total: 35
B b3 10/01/2023 15
Total: 15
Grand Total: 103
15 rows selected.
SQL>
If you, by any chance, use SQL*Plus, there are reporting settings you can use to get the same result:
SQL> alter session set nls_date_format = 'dd/mm/yyyy';
Session altered.
SQL> set pagesize 200
SQL> col style format a15
SQL> col color format a7
SQL> break on order_date on report
SQL> compute sum label "Total:" of order_qty on order_date
SQL> compute sum label "Grand Total:" of order_qty on report
SQL> select style, color, order_date, order_qty
2 from ep
3 order by order_date;
which results in
STYLE COLOR ORDER_DATE ORDER_QTY
--------------- ------- ---------- ----------
A a1 01/01/2023 10
A a2 12
A a3 15
********** ----------
Total: 37
B b1 05/01/2023 5
A a3 8
********** ----------
Total: 13
C c1 06/01/2023 3
********** ----------
Total: 3
H a1 07/01/2023 25
A b3 10
********** ----------
Total: 35
B b3 10/01/2023 15
********** ----------
Total: 15
----------
Grand Total: 103
9 rows selected.
SQL>