Home > Articles

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account