21:21

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Typical problems

About the use of the operator CASE

GROUP BY clause

Let's now receive the number of the computers those RAM-suitable to each type of operational system. Then we can write the following query:

Console

Execute

SELECT 
       CASE 
            WHEN ram < 64 
            THEN 'W95'
            WHEN ram < 128 
            THEN 'W98'
            ELSE 'W2k' 
        END Type,
        COUNT(*) Qty 
FROM PC
GROUP BY
        CASE 
             WHEN ram < 64 
             THEN 'W95'
             WHEN ram < 128 
             THEN 'W98'
             ELSE 'W2k' 
        END

As a result of running query we shall receive:

Type	Qty
W2k	5
W95	3
W98	3

Here we duplicate CASE operator in SELECT clause to receive a column with the name of operational system.

You can do the grouping by different number of columns using CASE operator within a single query. Let's consider the following task for example.

For each unique pair of values of processor's speed and hd capacity, determine average PC price. For the products with speed lower than $600, do grouping by a speed only.

For comparison, first let's obtain grouping by one column (speed) and by two columns (speed, hd) respectively.

Console

Execute

SELECT speed,MAX(hd) max_hd, AVG(price) avg_price  FROM pc 
GROUP BY speed;

speed	max_hd	avg_price
450	10	350.00
500	10	487.50
600	14	850.00
750	20	900.00
800	20	970.00
900	40	980.00

Console

Execute

SELECT speed, hd max_hd, AVG(price) avg_price  FROM pc
GROUP BY speed, hd
ORDER BY speed;

speed	max_hd	avg_price
450	8	350.00
450	10	350.00
500	5	600.00
500	10	375.00
600	8	850.00
600	14	850.00
750	14	850.00
750	20	950.00
800	20	970.00
900	40	980.00

Here each group has the same values in hd column, so max(hd) can be used instead of hd and vice versa.

Now is the solution of the problem:

Console

Execute

SELECT speed, MAX(hd) max_hd, AVG(price) avg_price  FROM pc
GROUP BY speed, CASE WHEN speed >= 600 THEN hd ELSE speed END
ORDER BY speed;

speed	max_hd	avg_price
450	10	350.00
500	10	487.50
600	8	850.00
600	14	850.00
750	14	850.00
750	20	950.00
800	20	970.00
900	40	980.00

When the condition speed >= 600 is true, the grouping is using the speed, hd columns, otherwise the grouping turns into

GROUP BY speed, speed

This is equivalent to grouping by a single column. By the way, operator CASE in the last solution could be written without ELSE part:

Console

Execute

SELECT speed, MAX(hd) max_hd, AVG(price) avg_price  FROM pc
GROUP BY speed, CASE WHEN speed >= 600 THEN hd END
ORDER BY speed;

Though the explicit grouping - GROUP BY speed, NULL - gives an error.

GROUP BY CASE

Previous [WHERE clause]

[HAVING clause] 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
several days ago

https://exchangesumo.com/obmen/WAVES-PPGBP/

SQL Problems and solutions

S. I. Moiseenko

Typical problems

About the use of the operator CASE

GROUP BY clause