   08:46

# 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  Console
`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

point    Qty
1    66500.00
2    13000.00
3    3100.00
ALL    82600.00

The problems analogous to mentioned above are being solved with special ROLLUP clause of SELECT statement. This opportunity makes the query very simple:  Console
`SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST(point AS varchar) END point, SUM(inc) QtyFROM 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:  Console
`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  Console
`SELECT CAST(point AS varchar) point, SUM(inc) Qty FROM Income GROUP BY pointUNION ALLSELECT 'ALL', SUM(inc)FROM Income;`

### FULL JOIN  Console
`SELECT coalesce(X.point,Y.point) point, coalesce(X.Qty,Y.Qty) Qty FROM(SELECT CAST(point AS varchar) point, SUM(inc) QtyFROM Income GROUP BY point) XFULL JOIN(SELECT 'ALL' point, SUM(inc) QtyFROM 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  