loading..
Ðóññêèé    English
23:29

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
  1. SELECT point, SUM(inc) Qty
  2. 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
  1. SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST(point AS varchar) END point,
  2. SUM(inc) Qty
  3. FROM Income
  4. 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
  1. SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST(point AS varchar) END point,
  2.     SUM(inc) Qty
  3.     FROM Income
  4.    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
  1. SELECT CAST(point AS varchar) point, SUM(inc) Qty
  2. FROM Income GROUP BY point
  3. UNION ALL
  4. SELECT 'ALL', SUM(inc)
  5. FROM Income;

FULL JOIN

Console
Execute
  1. SELECT coalesce(X.point,Y.point) point, coalesce(X.Qty,Y.Qty) Qty FROM
  2. (SELECT CAST(point AS varchar) point, SUM(inc) Qty
  3. FROM Income GROUP BY point) X
  4. FULL JOIN
  5. (SELECT 'ALL' point, SUM(inc) Qty
  6. 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

Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
today
https://exchangesumo.com/obmen/to/CNTEUR/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.