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
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]

As a result of running query we shall receive:

TypeQty
W2k5
W953
W983

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.

select speed,max(hd) max_hd, avg(price) avg_price  from pc
group by speed;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]
speedmax_hdavg_price
45010350
50010487.5
60014850
75020900
80020970
90040980
select speed, hd max_hd, avg(price) avg_price  from pc
group by speed, hd
order by speed;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]
speedmax_hdavg_price
4508350
45010350
5005600
50010375
6008850
60014850
75014850
75020950
80020970
90040980

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;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]
speedmax_hdavg_price
45010350
50010487.5
6008850
60014850
75014850
75020950
80020970
90040980

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;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]

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