23:39

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SELECT statement

Getting summarizing values

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) 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:

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 point
UNION ALL
SELECT '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) 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

grouping FULL JOIN UNION ALL ROLLUP

Previous [HAVING clause]

[Combination of detailed and aggregated data] Next

Last added:

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
month ago

https://exchangesumo.com/obmen/to/CNTEUR/ . Пошаговый план строительства.

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Getting summarizing values

Summarizing data using ROLLUP

UNION

FULL JOIN