CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Rosanero4Ever
Rosanero4Ever

Reputation: 492

Grouping and sum Quarter values


I have the following table in my SQL Server 2012 instance:

+---------+---------+
| Quarter | Values  |
+---------+---------+
| Q1      | 10      |
| Q2      | 15      |
| Q3      |  5      |
| Q4      |  1      |
+---------+---------+

I would like grouping quarter in order to sum values to obtain
Q1 = 25
Q2 = 31
So, Q2 sum also previous values that exist in Q1. If I had Q3 the sum would be Sum(Q1) + Sum (Q2) + Sum (Q3).
In this way a simple Group by clause doesn't work. Can anybody help me to find a solution in order to perform the described task using a simple select statement without using Union, please?

Upvotes: 3

Views: 1625

Answers (2)

NYCdotNet
NYCdotNet

Reputation: 4647

Note that you may need to change this query if you're dealing with multiple years.

--Setup sample table
DECLARE @MyData TABLE (
    TheQuarter VARCHAR(2) NOT NULL,
    TheValue INT NOT NULL
);

--insert sample data
INSERT INTO @MyData VALUES ('Q1',10),
    ('Q1',15),
    ('Q2',5),
    ('Q2',1);

WITH Totals AS (SELECT ROW_NUMBER() OVER (ORDER BY TheQuarter ASC) AS [QuarterIndex],
            TheQuarter, SUM(TheValue) AS [TotalValue]
        FROM @MyData GROUP BY TheQuarter)
    SELECT t.TheQuarter, SUM(tPlusPrev.TotalValue) AS [TotalValue]
    FROM Totals t
        LEFT OUTER JOIN Totals tPlusPrev ON t.QuarterIndex >= tPlusPrev.QuarterIndex
    GROUP BY t.TheQuarter;

Upvotes: 1

Nico
Nico

Reputation: 1197

Since you are using SQL 2012, you can also use RANGES

SELECT DISTINCT
  TheQuarter
  , SUM( TheValue ) OVER ( ORDER BY TheQuarter RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "RollingSum"
FROM 
  MyData

SQL-Fiddle

@edit:

If you are dealing with multiple years, see this fiddle.

Upvotes: 2

Related Questions