17:06

# 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
1. SELECT
2.        CASE
3.             WHEN ram < 64
4.             THEN 'W95'
5.             WHEN ram < 128
6.             THEN 'W98'
7.             ELSE 'W2k'
8.         END Type,
9.         COUNT(*) Qty
10. FROM PC
11. GROUP BY
12.         CASE
13.              WHEN ram < 64
14.              THEN 'W95'
15.              WHEN ram < 128
16.              THEN 'W98'
17.              ELSE 'W2k'
18.         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
1. SELECT speed,MAX(hd) max_hd, AVG(price) avg_price  FROM pc
2. 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
1. SELECT speed, hd max_hd, AVG(price) avg_price  FROM pc
2. GROUP BY speed, hd
3. 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
1. SELECT speed, MAX(hd) max_hd, AVG(price) avg_price  FROM pc
2. GROUP BY speed, CASE WHEN speed >= 600 THEN hd ELSE speed END
3. 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

1. 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
1. SELECT speed, MAX(hd) max_hd, AVG(price) avg_price  FROM pc
2. GROUP BY speed, CASE WHEN speed >= 600 THEN hd END
3. ORDER BY speed;

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

Tags
The book was updated
yesterday
https://exchangesumo.com/obmen/WAVES-PPGBP/