Home > Articles > Data > SQL

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

  • + Share This
  • 🔖 Save To Your Account