loading..
Ðóññêèé    English
23:53

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.

Bookmark and Share
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
month ago
https://exchangesumo.com/obmen/WAVES-PPGBP/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.