- 10.1 Introduction
- 10.2 Grouping on One Column
- 10.3 Grouping on Two or More Columns
- 10.4 Grouping on Expressions
- 10.5 Grouping of NULL Values
- 10.6 General Rules for the GROUP BY Clause
- 10.7 Complex Examples with GROUP BY
- 10.8 Grouping with WITH ROLLUP
- 10.9 Grouping with WITH CUBE
- 10.10 Grouping Sets
- 10.11 Grouping with ROLLUP and CUBE
- 10.12 Combining Grouping Sets
- 10.13 Answers
This chapter is from the book
This chapter is from the book
This chapter is from the book
10.4 Grouping on Expressions
Up to now, we have shown only examples in which the result was grouped on one or more columns, but what happens when we group on expressions? Again, here are two examples.
Example 10.8. For each year present in the PENALTIES table, get the number of penalties paid.
SELECT YEAR(PAYMENT_DATE), COUNT(*) FROM PENALTIES GROUP BY YEAR(PAYMENT_DATE)
The intermediate result from the GROUP BY clause is:
YEAR(PAYMENT_DATE) PAYMENTNO PLAYERNO PAYMENT_DATE AMOUNT
------------------ --------- ---------- ------------ --------
1980 {1, 5, 6} {6, 44, 8} {1980-12-08, {100.00,
1980-12-08, 25,00,
1980-12-08} 25,00}
1981 {2} {44} {1981-05-05} {75,00}
1982 {7} {44} {1982-12-30} {30,00}
1983 {3} {27} {1983-09-10} {100,00}
1984 {4, 8} {104, 27} {1984-12-08, {50,00,
1984-11-12} 75,00}
The result is:
YEAR(PAYMENT_DATE) COUNT(*) ------------------ -------- 1980 3 1981 1 1982 1 1983 1 1984 2
Explanation: The result is now grouped on the values of the scalar expression YEAR(PAYMENT_DATE). Rows for which the value of the expression YEAR(PAYMENT_DATE) is equal form a group.
Example 10.9. Group the players on the basis of their player numbers. Group 1 should contain the players with number 1 up to and including 24. Group 2 should contain the players with numbers 25 up to and including 49, and so on. For each group, get the number of players and the highest player number.
SELECT TRUNCATE(PLAYERNO/25,0), COUNT(*), MAX(PLAYERNO) FROM PLAYERS GROUP BY TRUNCATE(PLAYERNO/25,0)
The result is:
TRUNCATE(PLAYERNO/25,0) COUNT(*) MAX(PLAYERNO)
----------------------- -------- -------------
0 4 8
1 4 44
2 1 57
3 2 95
4 3 112
The scalar expression on which is grouped can be rather complex. This can consist of system variables, functions and calculations. Even certain scalar subqueries are allowed. Section 10.7 gives a few examples.
Exercise 10.10: Group the players on the length of their names and get the number of players for each length.
Exercise 10.11: For each match, determine the difference between the number of sets won and lost, and group the matches on that difference.