CODEWITHSUNDEEP
sqlsql-servergroupingwindow-functions
George Eivaz
George Eivaz

Reputation: 145

SQL Server Multiple Groupings

I am stumped by what seems like a simple problem. We have the following Table.

ID---   ---Income---       ---Years Offset---       ---Income By Offset---
 1          1000                 1                         NULL
 2           500                 1                         NULL
 3           400                 1                         NULL
 4            0                  1                         NULL
 5          2000                 2                         NULL
 6            0                  2                         NULL              
 7           400                 2                         NULL

What I would love to figure out how to do is to sum all of the income column by the "Years Offset column" and place in the first row of the "Income by Offset column." What would be awesome is if the Income by Offset column has values of 1900 in row 1 and 2400 in row 5 with the rest of them rows being untouched.

I know that this sound like a simple problem. But I have tried Window functions, Row_number(), SELF joining tables and a piece of it is solved with each but am having trouble putting it all together.

Thanks in advance, George

Upvotes: 0

Views: 76

Answers (3)

Stephan
Stephan

Reputation: 6018

My Version of Your Table

DECLARE @yourTable TABLE (ID INT,Income INT,[Years Offset] INT,[Income By Offset] INT NULL);

INSERT INTO @yourTable
VALUES  (1,1000,1,NULL),
        (2,500,1,NULL),
        (3,400,1,NULL),
        (4,0,1,NULL),
        (5,2000,2,NULL),
        (6,0,2,NULL),
        (7,400,2,NULL);

Actual Query

SELECT  ID,
        Income,
        [Years Offset],
        CASE
            WHEN ID = MIN(ID) OVER (PARTITION BY [Years Offset])
                THEN SUM(Income) OVER (PARTITION BY [Years Offset])
            ELSE [Income By Offset]
        END AS [Income By Offset]
FROM @yourTable

Results

ID          Income      Years Offset Income By Offset
----------- ----------- ------------ ----------------
1           1000        1            1900
2           500         1            NULL
3           400         1            NULL
4           0           1            NULL
5           2000        2            2400
6           0           2            NULL
7           400         2            NULL

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

This should return the required result set:

  SELECT ID, Income, [Years Offset],
         CASE WHEN ROW_NUMBER() OVER (PARTITION By [Years Offset] 
                                      ORDER BY ID) = 1
            THEN SUM(Income) OVER (PARTITION BY [Years Offset])
              ELSE NULL
         END AS [Income By Offset]
    FROM mytable

Windowed version of SUM calculates the Income per [Years Offset]. ROW_NUMBER() is used to return this value only for the first row of each [Years Offset] group.

Demo here

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7753

How about:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Income
(
  ID INT PRIMARY KEY,
  Income INT NOT NULL,
  YearsOffset Int NOT NULL,
  IncomeByOffset INT NULL
 )

 INSERT INTO Income (ID, Income, YearsOffset)
 VALUES (1,1000,1),
        (2,500,1),
        (3,400,1),
        (4,0,1),
        (5, 2000, 2),
        (6,0,2),
        (7,400,2)

Query 1:

UPDATE Income
  SET IncomeByOffset = I.IncomeByOffset
From 
(
  SELECT YearsOffset, SUM(Income) As IncomeByOffset, Min(ID) As MinId
  FROM Income
  GROUP BY YearsOffset
 ) I
 WHERE Income.YearsOffset = I.YearsOffset
   AND Income.Id = I.MinId

Results: Query 2:

SELECT *
FROM Income;

Results:

| ID | Income | YearsOffset | IncomeByOffset |
|----|--------|-------------|----------------|
|  1 |   1000 |           1 |           1900 |
|  2 |    500 |           1 |         (null) |
|  3 |    400 |           1 |         (null) |
|  4 |      0 |           1 |         (null) |
|  5 |   2000 |           2 |           2400 |
|  6 |      0 |           2 |         (null) |
|  7 |    400 |           2 |         (null) |

Upvotes: -1

Related Questions