Hello all,
suppose I have the following table (10.2, with SqlDev):
create table sales_z(
invno number,
custno number,
invtotal number(10,2),
invdate date);
insert into sales_z values(1, 10, 100.50, to_date('01-SEP-17', 'DD-MON-YY'));
insert into sales_z values(2, 10, 200.50, to_date('12-SEP-17', 'DD-MON-YY'));
insert into sales_z values(3, 10, 300.50, to_date('21-SEP-17', 'DD-MON-YY'));
insert into sales_z values(4, 10, 50.50, to_date('03-OCT-17', 'DD-MON-YY'));
insert into sales_z values(5, 10, 60.50, to_date('31-OCT-17', 'DD-MON-YY'));
insert into sales_z values(6, 10, 10.50, to_date('01-NOV-17', 'DD-MON-YY'));
insert into sales_z values(7, 10, 10.50, to_date('01-NOV-17', 'DD-MON-YY'));
insert into sales_z values(8, 10, 10.50, to_date('02-NOV-17', 'DD-MON-YY'));
insert into sales_z values(9, 10, 10.50, to_date('01-NOV-17', 'DD-MON-YY'));
insert into sales_z values(10, 90, 590.50, to_date('01-OCT-17', 'DD-MON-YY'));
insert into sales_z values(11, 50, 700.50, to_date('01-NOV-17', 'DD-MON-YY'));
And when I query for a particular month, I get this:
select sum(invtotal) "total for october"
from sales_z
where custno = 10 and
invdate >= '17-10-01' and
invdate < '17-11-01';
"total for october"
111
But how would I write a query do fetch sum(invtotal) for a given client (10) by month, e.g:
"custno" "month" "total by month"
10 SEP $601.50
10 OCT $111.00
10 NOV $42.00
Thank you!