CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
user2834105
user2834105

Reputation: 67

Sum of Each group with the actual data

I have been trying to do this in various ways but couldn't get exact solution so any help would be appreciated

Source:

Name    ID   Project   SLA
Ab      1     App      100
bc      2     App       80
cd      3     Bap      200
Ef      4     Vap      30
Fg      5     Vap      70

Target : 

Name    ID   Project   SLA
Ab      1     App      100
bc      2     App       80
Null    Null  App      180
cd      3     Bap      200
Null    Null  Bap      200
Ef      4     Vap      30
Fg      5     Vap      70
Null    Null  Vap      100

In SQL

Upvotes: 0

Views: 50

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

You can use this one:

WITH t AS 
   (SELECT NAME, ID, PROJECT, SUM(SLA) AS SUM_SLA, GROUPING_ID(NAME, ID) AS GROUPING_ID
   FROM SLA
   GROUP BY ROLLUP(NAME, ID), PROJECT)
SELECT NAME, ID, PROJECT, SUM_SLA
FROM t
WHERE GROUPING_ID IN (0,3);

or even more compact

SELECT NAME, ID, PROJECT, SUM(SLA) AS SUM_SAL
FROM SLA
GROUP BY PROJECT, GROUPING SETS((NAME, ID), PROJECT);

However, I don't know whether it is supported by SQL-Server

Upvotes: 0

jarlh
jarlh

Reputation: 44795

select Name, ID, Project, SLA
from tab
union all
select null, null, Project, SUM(SLA)
from tab
group by project
order by project, id

ANSI/ISO standard SQL, should be portable. (Posting was tagged with several different dbms before...)

Upvotes: 2

Related Questions