How to add a subtotal row in sql [closed]

I have a table with following data:

Name Score
A     2
B     3
A     1
B     3

I want a query which give the following output.

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

Please help me with an SQL code

Some DBMS (like MySQL and SQL-Server) have the WITH ROLLUP modifier of the GROUP BY clause, that can be used for such a query:

SELECT pk, name,
       SUM(score) AS score
FROM tableX
GROUP BY name, pk                   -- pk is the PRIMARY KEY of the table
  WITH ROLLUP ;

Test at SQL-Fiddle

Other DBMS (SQL-Server, Oracle) have implemented the GROUP BY GROUPING SETS feature which can be used (and is more powerful than WITH ROLLUP):

SELECT pk, name,
       SUM(score) AS score
FROM tableX
GROUP BY GROUPING SETS 
  ((name, pk), (name), ());

Test at SQL-Fiddle-2

@AndriyM corrected me that there is also the GROUP BY ROLLUP (implemented by Oracle and SQL-Server), with same results (see updated SQL-Fiddle-2 above):

SELECT pk, name,
       SUM(score) AS score
FROM t
GROUP BY ROLLUP 
  (name, pk) ;

In general, it would be something like this:

select Name, Score
from (
    select Name, Score, Name as o
    from Table1 as a
    union all
    select 'Subtotal', sum(Score), Name as o
    from Table1 as a
    group by Name
) as a
order by o, score

But, just as @ypercube said, there're different specific implementations in different RDBMS. Your task a bit complicated, because your table doesn't have primary key, so you can emulate it with row_number() function. For SQL Server you can use grouping sets:

with cte as (
    select *, row_number() over(order by newid()) as rn
    from Table1
)
select
    case
       when grouping(c.rn) = 1 then 'Subtotal'
       else c.Name
    end as Name,
    sum(c.Score) as Score
from cte as c
group by grouping sets ((c.Name), (c.Name, c.rn))
order by c.Name;

Or rollup():

with cte as (
    select *, row_number() over(order by newid()) as rn
    from Table1
)
select
    case
       when grouping(c.rn) = 1 then 'Subtotal'
       else c.Name
    end as Name,
    sum(c.Score) as Score
from cte as c
group by rollup(c.Name, c.rn)
having grouping(c.Name) = 0
order by c.Name;

Note the grouping() function to replace name column for static 'Subtotal' string. Also note, that order of columns matters in rollup() query.

=> sql fiddle demo

What you are showing is more of a report. You can use application logic or a reporting tool. The first query shows the sorted list of values by player and the second query gets the actual sum.

select
    name,
    score
from playerscore
order by name asc

select
    name,
    sum(score)
from playerscore
group by name

How to add a subtotal row in sql [closed]

Related

Recent Posts