loading..
   English
03:16

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

Bookmark and Share
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
Source . crashsurvivorsnetwork.org
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100