- 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 Grouping with Sorting
- 10.7 General Rules for the GROUP BY Clause
- 10.8 The GROUP_CONCAT Function
- 10.9 Complex Examples with GROUP BY
- 10.10 Grouping with WITH ROLLUP
- 10.11 Answers
This chapter is from the book
This chapter is from the book
This chapter is from the book
10.4 Grouping on Expressions
Until 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? See the next two examples.
Example 10.8: For each year 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 rows are grouped can be rather complex. This can consist of system variables, user variables, functions, and calculations. Even certain scalar subqueries are allowed.
Exercise 10.9: Group the players on the length of their names and get the number of players for each length.
Exercise 10.10: For each match, determine the difference between the number of sets won and lost, and group the matches on that difference.
Exercise 10.11: For each combination of year-month in the COMMITTEE_MEMBERS table, get the number of committee members who started in that year and that month.