loading..
   English
23:26

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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100