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:
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
[[ column ]] |
---|
NULL [[ value ]] |
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 comparison, first let’s obtain grouping by one column (speed) and by two columns (speed, hd) respectively.
select speed,max(hd) max_hd, avg(price) avg_price from pc
group by speed;
[[ column ]] |
---|
NULL [[ value ]] |
speed | max_hd | avg_price |
---|---|---|
450 | 10 | 350 |
500 | 10 | 487.5 |
600 | 14 | 850 |
750 | 20 | 900 |
800 | 20 | 970 |
900 | 40 | 980 |
select speed, hd max_hd, avg(price) avg_price from pc
group by speed, hd
order by speed;
[[ column ]] |
---|
NULL [[ value ]] |
speed | max_hd | avg_price |
---|---|---|
450 | 8 | 350 |
450 | 10 | 350 |
500 | 5 | 600 |
500 | 10 | 375 |
600 | 8 | 850 |
600 | 14 | 850 |
750 | 14 | 850 |
750 | 20 | 950 |
800 | 20 | 970 |
900 | 40 | 980 |
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:
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;
[[ column ]] |
---|
NULL [[ value ]] |
speed | max_hd | avg_price |
---|---|---|
450 | 10 | 350 |
500 | 10 | 487.5 |
600 | 8 | 850 |
600 | 14 | 850 |
750 | 14 | 850 |
750 | 20 | 950 |
800 | 20 | 970 |
900 | 40 | 980 |
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:
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;
[[ column ]] |
---|
NULL [[ value ]] |
Though the explicit grouping - GROUP BY speed, NULL - gives an error.