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 coincidings, are necessary to us.

Suggested exercises: 120