01:31

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
Execute
`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
Execute
`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
Execute
`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
Execute
`SELECT CAST(point AS varchar) point, SUM(inc) Qty FROM Income GROUP BY pointUNION ALLSELECT 'ALL', SUM(inc)FROM Income;`

FULL JOIN

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

Last added:
Tags
The book was updated
month ago
https://exchangesumo.com/obmen/to/CNTEUR/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.