Summarizing data using ROLLUP

Let’s compute total income funds per each point of reception in the Income table. This is easy to do with the query

select point, sum(inc) Qty
from Income 
group by point;

Assume that you need to get sum over all the points in addition, i.e. the result should look like this

The problems analogous to mentioned above are being solved with special ROLLUP clause of SELECT statement. This opportunity makes the query very simple:

SELECT 
    CASE 
        WHEN point IS NULL 
        THEN 'ALL' 
        ELSE CAST(point AS VARCHAR) 
    END point,
    SUM(inc) Qty
FROM Income 
GROUP BY point WITH ROLLUP;

Due to the fact that the values of a column must be of the same data type, point number convert to the character data type.

The last query can be rewritten in another (standard) syntax form:

select 
    case 
        when point is null 
        then 'ALL' 
        else cast(point as varchar) 
    end point,
    sum(inc) Qty
from Income group by ROLLUP(point);

If DBMS does not support the ROLLUP clause, you can use either UNION or outer join (FULL JOIN) to combine two queries into one.

These solutions are Below.

UNION

select cast(point as varchar) point, sum(inc) Qty
from Income 
group by point
union ALL
select 'ALL', sum(inc)
from Income;

FULL JOIN

select coalesce(X.point,Y.point) point, coalesce(X.Qty,Y.Qty) Qty 
from (select cast(point as varchar) point, sum(inc) Qty
      from Income group by point
     ) X
   full join
     (select 'ALL' point, sum(inc) Qty
      from Income
     ) Y on 1 = 2;

Note that the last solution use the joining on obviously false predicate because rows from both tables, which have no coinciding, are necessary to us.

Suggested exercises: 120