Say, I have the following data:
select 1 id, date '2007-01-16' date_created, 5 sales, 'Bob' name from dual union all
select 2 id, date '2007-04-16' date_created, 2 sales, 'Bob' name from dual union all
select 3 id, date '2007-05-16' date_created, 6 sales, 'Bob' name from dual union all
select 4 id, date '2007-05-21' date_created, 4 sales, 'Bob' name from dual union all
select 5 id, date '2013-07-16' date_created, 24 sales, 'Bob' name from dual union all
select 6 id, date '2007-01-17' date_created, 15 sales, 'Ann' name from dual union all
select 7 id, date '2007-04-17' date_created, 12 sales, 'Ann' name from dual union all
select 8 id, date '2007-05-17' date_created, 16 sales, 'Ann' name from dual union all
select 9 id, date '2007-05-22' date_created, 14 sales, 'Ann' name from dual union all
select 10 id, date '2013-07-17' date_created, 34 sales, 'Ann' name from dual
I want to get results like the following:
Name Total_cumulative_sales Total_sales_current_month
Bob 41 24
Ann 91 34
In this table, for Bob, his total sales is 41 starting from the beginning. And for this month which is July, his sales for this entire month is 24. Same goes for Ann.
How do I write an SQL to get this result?
Try this way:
select name, sum(sales) as Total_cumulative_sales ,
sum(
case trunc(to_date(date_created), 'MM')
when trunc(sysdate, 'MM') then sales
else 0
end
) as Total_sales_current_month
from tab
group by name
More information