How do I write an SQL to get a cumulative value and a monthly total in one row?

Question

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?

Robert · Accepted Answer · 2013-07-16T09:53:48.613

6

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

SQL Fiddle Demo

More information

edited Jul 16 '13 at 09:53

answered Jul 16 '13 at 09:48

Robert

25,425
8
67
81

I think this adds sales from the same month but over different years. Is this what is wanted? – Darkzaelus Jul 16 '13 at 10:43
@Darkzaelus [See this link](http://sqlfiddle.com/#!4/92865/1/0) I've changed first row to `2007-07-16` and it still works – Robert Jul 16 '13 at 10:43
Then I retract, apologies :) I'll have to read up on the `trunc` function to figure out how that works – Darkzaelus Jul 16 '13 at 10:56
Ooooh it doesn't just bring back the month, it returns it with the day and any other less relevant data basically rounded down. Clever – Darkzaelus Jul 16 '13 at 11:08

score 2 · Answer 2 · answered Jul 16 '13 at 09:33

SELECT Name, 
SUM(Sales) Total_sales, 
SUM(CASE WHEN MONTH(date_created) = MONTH(GetDate()) AND YEAR(date_created) = YEAR(GetDate()) THEN Sales END) Total_sales_current_month
GROUP BY Name

Should work, but there's probably a more elegant way to specify "in the current month".

score 2 · Answer 3 · answered Jul 16 '13 at 10:21

This should work for sales over a number of years. It will get the cumulative sales over any number of years. It won't produce a record if there are no sales in the latest month.

    WITH sales AS 
(select 1 id, date '2007-01-16' date_created, 5 sales, 'Bob' sales_name from dual union all
  select 2 id, date '2007-04-16' date_created, 2 sales, 'Bob' sales_name from dual union all
  select 3 id, date '2007-05-16' date_created, 6 sales, 'Bob' sales_name from dual union all
  select 4 id, date '2007-05-21' date_created, 4 sales, 'Bob' sales_name from dual union all
  select 5 id, date '2013-07-16' date_created, 24 sales, 'Bob' sales_name from dual union all
  select 6 id, date '2007-01-17' date_created, 15 sales, 'Ann' sales_name from dual union all
  select 7 id, date '2007-04-17' date_created, 12 sales, 'Ann' sales_name from dual union all
  select 8 id, date '2007-05-17' date_created, 16 sales, 'Ann' sales_name from dual union all
  select 9 id, date '2007-05-22' date_created, 14 sales, 'Ann' sales_name from dual union all
  select 10 id, date '2013-07-17' date_created, 34 sales, 'Ann' sales_name from dual)
  SELECT  sales_name
         ,total_sales
         ,monthly_sales
         ,mon 
  FROM (SELECT sales_name
              ,SUM(sales) OVER (PARTITION BY sales_name ORDER BY mon) total_sales
              ,SUM(sales) OVER (PARTITION BY sales_name,mon ORDER BY mon) monthly_sales
              ,mon 
              ,max_mon
        FROM (  SELECT sales_name
                      ,sum(sales) sales
                      ,mon
                      ,max_mon
                FROM   (SELECT sales_name
                              ,to_number(to_char(date_created,'YYYYMM')) mon
                              ,sales
                              ,MAX(to_number(to_char(date_created,'YYYYMM'))) OVER (PARTITION BY sales_name) max_mon
                        FROM sales
                        ORDER BY 2)
                GROUP BY sales_name
                        ,max_mon
                        ,mon
             )
        )
WHERE max_mon = mon

;

How do I write an SQL to get a cumulative value and a monthly total in one row?

3 Answers3

SQL Fiddle Demo