SQL Sum cumulative and non-cumulative in same query

Question

Hi I was wondering if there is a way to get a cumulative and non-cumulative total in the same query. I have a table with following fields: Department, SalesPerson, fin_month, activity, cost

What I would like is have two sums, one that would give a monthly total for salesperson, and another giving a year to date total. I am having a problem setting two different where criteria to get it to work.

Many Thanks

I spent about an hour here before seeing this and being done in 5 minutes. http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — smoore4, Oct 02 '13 at 14:35

score 0 · Answer 1 · answered Jun 20 '11 at 08:59

Would something like this help?

SELECT 
* 
FROM
(
SELECT 
Department, SalesPerson
,   SUM(fin_month) SalesPerson_Sum
FROM 
[TABLE_NAME]
GROUP BY Department, SalesPerson
) a 
INNER JOIN 
(
SELECT 
    Department
, SUM(fin_month) AS Department_Sum
    FROM 
[TABLE_NAME]
GROuP BY 
    Department
) b
ON 
a.Department = b.Department

score 0 · Answer 2 · answered Jun 20 '11 at 16:54

This solution uses CTEs, recursion, and ranking to obtain cumulative totals for every fin_month per SalesPerson in every Department based on the corresponding monthly totals.

;WITH
  monthlytotals AS (
    SELECT
      Department,
      SalesPerson,
      fin_month,
      MonthlyTotal = SUM(cost),
      rn = ROW_NUMBER() OVER (PARTITION BY Department, SalesPerson
                                  ORDER BY fin_month)
    FROM atable
    GROUP BY Department, SalesPerson, fin_month
  ),
  alltotals AS (
    SELECT
      Department,
      SalesPerson,
      fin_month,
      MonthlyTotal,
      CumulativeTotal = MonthlyTotal,
      rn
    FROM monthlytotals
    WHERE rn = 1
    UNION ALL
    SELECT
      m.Department,
      m.SalesPerson,
      m.fin_month,
      m.MonthlyTotal,
      CumulativeTotal = a.CumulativeTotals + m.MonthlyTotal,
      m.rn
    FROM monthlytotals m
      INNER JOIN alltotals a
         ON m.Department = a.Department
        AND m.SalesPerson = a.SalesPerson
        AND m.rn = a.rn + 1
  )
SELECT
  Department,
  SalesPerson,
  fin_month,
  MonthlyTotal,
  CumulativeTotal
FROM alltotals

SQL Sum cumulative and non-cumulative in same query

2 Answers2