CASE statement page 3

Let's consider a few more examples.

Determine number of trips from Rostov to Moscow, and number of trips to other towns (not to Moscow).

Here we can use additional computed column for grouping by it.

  1. SELECT flag, COUNT(*) qty FROM
  2. (SELECT CASE WHEN town_to ='Moscow' THEN 'Moscow' ELSE 'Other' END flag
  3. FROM Trip
  4. WHERE town_from='Rostov'
  5. ) X
  6. GROUP BY flag;

flag qty
Moscow 4
Other 2

Determine total number of trips from Rostov, and number of trips from Rostov to towns other than Moscow.

This problem requires to do grouping by two sets also, but now one set is a subset of another one. So exactly the same approach based on computed column is not fit here. In the previous task we had two non-intersecting sets to do grouping for each.

To solve this task we can calculate count over total set and use subquery to calculate the count over subset (second referense to a table), or we can use CASE statement in the conjunction  with aggregate function to avoid second reading of a table. Let's check the estimation of optimizer for these two variants.

Use of subquery

  1. SELECT COUNT(*) total,
  3. WHERE town_from='Rostov' AND town_to <> 'Moscow') non_moscow
  4. FROM Trip
  5. WHERE town_from='Rostov';

 Use of CASE with aggregate function

  1. SELECT COUNT(*) total_qty,
  2. SUM(CASE WHEN town_to <>'Moscow' THEN 1 ELSE 0 END) non_moscow
  3. FROM Trip
  4. WHERE town_from='Rostov';

The result will be the same naturally:

total non_moscow
6 2

but the cost of the second query, as was expected,  is twice as less than of the first one.

You can compare the real run time of these queries on sufficient large amount of data.

The second variant can be rewritten in more short form with use of NULLIF function:

  1. SELECT COUNT(*) total_qty,
  2. COUNT(NULLIF(town_to, 'Moscow')) non_moscow
  3. FROM Trip
  4. WHERE town_from='Rostov';

NULLIF function returns NULL, if its arguments are equal, or the first one otherwise.

The solution exploits the fact that the aggregate functions don't take into account NULL values which arise in argument of COUNT function when the landing town is 'Moscow'.

Bookmark and Share
Pages 1 2 3 4
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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.