CODEWITHSUNDEEP
sqlsql-server
Hardik
Hardik

Reputation: 41

Get rollup group value in SQL Server

I have a table with following data:

Name Score
A 2
B 3
A 1
B 3

I want a query which returns the following output.

Name Score
A 2
A 1
Subtotal: A 3
B 3
B 3
Subtotal: B 6

I am able to get "Subtotal" with group by rollup query but I want to get subtotal along with group column value.

Please help me with some SQL code

Upvotes: 0

Views: 259

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If score has at most one value per name, you can use GROUPING SETS`:

select name, sum(score) as score
from t
group by grouping sets ((name, score), (name));

If name is never null, I would just use:

coalesce(name, 'Grouping ' + name)

Otherwise you need to use grouping().

Upvotes: 1

Related Questions