CODEWITHSUNDEEP
sql-servergroup-bysum
user3294322
user3294322

Reputation: 97

SQL Server 2008: Calculate SUM in new row (group by)

I have a table with following:

CREATE TABLE #tableA (color varchar(10), ids int, scores int)
INSERT INTO #tableA 
VALUES ('red', 1, 100),
('red',2,100),
('red',3,50),
('blue',1,20),
('blue',5,50)

I want to get the SUM of scores ,group by color. Expected result:

color  id  scores
red    1   100
red    2   100
red    3    50
           SUM 250 (100+100+50)  
blue   1    20
blue   5    50
            SUM 70 (all blues)

Thanks

Upvotes: 0

Views: 226

Answers (2)

Lamak
Lamak

Reputation: 70658

You can use GROUPING SETS for this, although the results won't be directly in the format you want:

SELECT  color, 
        ids, 
        SUM(scores) Scores
FROM #TableA
GROUP BY GROUPING SETS((color,ids),(color))

The results are:

╔═══════╦══════╦════════╗
║ color ║ ids  ║ Scores ║
╠═══════╬══════╬════════╣
║ blue  ║ 1    ║     20 ║
║ blue  ║ 5    ║     50 ║
║ blue  ║ NULL ║     70 ║
║ red   ║ 1    ║    100 ║
║ red   ║ 2    ║    100 ║
║ red   ║ 3    ║     50 ║
║ red   ║ NULL ║    250 ║
╚═══════╩══════╩════════╝

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93724

Use ROllUP in Group by

SELECT *
FROM   (SELECT color,
               ids,
               Sum(scores)scores
        FROM   #tableA
        GROUP  BY color,
                  ids WITH rollup) a
WHERE  color IS NOT NULL
        OR ids IS NOT NULL

Upvotes: 2

Related Questions