Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Apr 30, 2007

SQL for MySQL Developers: A Comprehensive Tutorial and Reference - Page 4

By DatabaseJournal.com Staff

10.9 Complex Examples with GROUP BY

Consider the following examples that illustrate the extensive possibilities of the GROUP BY clause.

Example 10.17: What is the average total amount of penalties for players who live in Stratford and Inglewood?

SELECT   AVG(TOTAL)
FROM    (SELECT   PLAYERNO, SUM(AMOUNT) AS TOTAL
         FROM     PENALTIES
         GROUP BY PLAYERNO) AS TOTALS
WHERE    PLAYERNO IN 
        (SELECT   PLAYERNO
         FROM     PLAYERS
         WHERE    TOWN = 'Stratford' OR TOWN = 'Inglewood')

The result is:

AVG(TOTAL)
----------
        85

Explanation: The intermediate result of the subquery in the FROM clause is a table consisting of two columns, called PLAYERNO and TOTAL, and five rows (players 6, 8, 27, 44, and 104). This table is passed on to the WHERE clause, in which a subquery is used to select players from Stratford and Inglewood (players 6, 8, and 44). Finally, the average is calculated in the SELECT clause of the column TOTAL.

Example 10.18: For each player (who incurred penalties and is captain), get the player number, name, number of penalties that he or she incurred, and number of teams that he or she captains.

SELECT   PLAYERS.PLAYERNO, NAME, NUMBER_OF_PENALTIES,
         NUMBER_OF_TEAMS
FROM     PLAYERS,
        (SELECT   PLAYERNO, COUNT(*) AS NUMBER_OF_PENALTIES
         FROM     PENALTIES
         GROUP BY PLAYERNO) AS NUMBER_PENALTIES,
        (SELECT   PLAYERNO, COUNT(*) AS NUMBER_OF_TEAMS
         FROM     TEAMS
         GROUP BY PLAYERNO) AS NUMBER_TEAMS
WHERE    PLAYERS.PLAYERNO = NUMBER_PENALTIES.PLAYERNO
AND      PLAYERS.PLAYERNO = NUMBER_TEAMS.PLAYERNO

The result is:

PLAYERNO  NAME       NUMBER_OF_PENALTIES  NUMBER_OF_TEAMS
--------  ---------  -------------------  ---------------
       6  Parmenter                    1                1
      27  Collins                      2                1

Explanation: The FROM clause contains two subqueries that both have a GROUP BY clause.

We could more easily formulate the previous statement by including subqueries in the SELECT clause, which eliminates the need for GROUP BY clauses. See the next example, and note that the only difference is that all players appear in the result.

SELECT   PLAYERS.PLAYERNO, NAME,
        (SELECT   COUNT(*) 
         FROM     PENALTIES
         WHERE    PLAYERS.PLAYERNO =
                  PENALTIES.PLAYERNO) AS NUMBER_OF_PENALTIES,
        (SELECT   COUNT(*)
         FROM     TEAMS
         WHERE    PLAYERS.PLAYERNO =
                  TEAMS.PLAYERNO) AS NUMBER_OF_TEAMS
FROM     PLAYERS

Example 10.19: Get the player number and total number of penalties for each player who played a match.

SELECT   DISTINCT M.PLAYERNO, NUMBERP
FROM     MATCHES AS M LEFT OUTER JOIN
           (SELECT   PLAYERNO, COUNT(*) AS NUMBERP
            FROM     PENALTIES
            GROUP BY PLAYERNO) AS NP
         ON M.PLAYERNO = NP.PLAYERNO

Explanation: In this statement, the subquery creates the following intermediate result (this is the NP table):

PLAYERNO  NUMBERP
--------  -------
       6        1
       8        1
      27        2
      44        3
     104        1

Next, this table is joined with the MATCHES table. We execute a left outer join, so no players will disappear from this table. The final result is:

PLAYERNO  NUMBERP
--------  -------
       2        ?
       6        1
       8        1
      27        2
      44        3
      57        ?
      83        ?
     104        1
     112        ?

Example 10.20: Group the penalties on the basis of payment date. Group 1 should contain all penalties between January 1, 1980, and June 30, 1982; group 2 should contain all penalties between July 1, 1981, and December 31, 1982; and group 3 should contain all penalties between January 1, 1983, and December 31, 1984. For each group, get the sum of all penalties.

SELECT   GROUPS.PGROUP, SUM(P.AMOUNT)
FROM     PENALTIES AS P,
        (SELECT 1 AS PGROUP, '1980-01-01' AS START, 
                '1981-06-30' AS END
         UNION
         SELECT 2, '1981-07-01', '1982-12-31'
         UNION
         SELECT 3, '1983-01-01', '1984-12-31') AS GROUPS
WHERE    P.PAYMENT_DATE BETWEEN START AND END
GROUP BY GROUPS.PGROUP
ORDER BY GROUPS.PGROUP

The result is:

GROUP  SUM(P.AMOUNT)
-----  -------------
    1         225.00
    2          30.00
    3         225.00

Explanation: In the FROM clause, a new (virtual) table is created in which the three groups have been defined. This GROUPS table is joined with the PENALTIES table. A BETWEEN operator is used to join the two tables. Penalties with a payment date that falls outside these groups will not be included in the result.

Example 10.21: For each penalty, get the penalty amount plus the sum of that amount and the amounts of all penalties with a lower payment number (cumulative value).

SELECT   P1.PAYMENTNO, P1.AMOUNT, SUM(P2.AMOUNT)
FROM     PENALTIES AS P1, PENALTIES AS P2
WHERE    P1.PAYMENTNO >= P2. PAYMENTNO
GROUP BY P1. PAYMENTNO, P1.AMOUNT
ORDER BY P1. PAYMENTNO

For convenience, assume that the PENALTIES table consists of the following three rows only (you can create this, too, by temporarily removing all penalties with a number greater than 3):

PAYMENTNO  PLAYERNO  PAYMENT_DATE  AMOUNT
---------  --------  ------------  ------
        1         6  1980-12-08       100
        2        44  1981-05-05        75
        3        27  1983-09-10       100

The desired result is:

PAYMENTNO  AMOUNT    SUM
---------  ------  -----
        1     100    100
        2      75    175
        3     100    275

The intermediate result of the FROM clause (showing only the columns PAYMENTNO and AMOUNT):

P1.PAYNO  P1.AMOUNT  P2.PAYNO  P2.AMOUNT
--------  ---------  --------  ---------
       1        100         1        100
       1        100         2         75
       1        100         3        100
       2         75         1        100
       2         75         2         75
       2         75         3        100
       3        100         1        100
       3        100         2         75
       3        100         3        100

The intermediate result of the WHERE clause:

P1.PAYNO  P1.AMOUNT  P2.PAYNO  P2.AMOUNT
--------  ---------  --------  ---------
       1        100         1        100
       2         75         1        100
       2         75         2         75
       3        100         1        100
       3        100         2         75
       3        100         3        100

The intermediate result of the GROUP BY clause:

P1.PAYNO  P1.AMOUNT  P2.PAYNO   P2.AMOUNT
--------  ---------  ---------  --------------
       1        100  {1}        {100}
       2         75  {1, 2}     {100, 75}
       3        100  {1, 2, 3}  {100, 75, 100}

The intermediate result of the SELECT clause:

P1.PAYNO  P1.AMOUNT  SUM(P2.AMOUNT)
--------  ---------  --------------
       1        100             100
       2         75             175
       3        100             275

This final result is equal to the desired table.

Most joins in this book (and in the real world) are equi joins. Non-equi joins are rare. However, the previous statement shows an example where non-equi joins can be useful and the powerful statements they can formulate.

Example 10.22: For each penalty, get the payment number, penalty amount, and percentage that the amount forms of the sum of all amounts (use the same PENALTIES table as in the previous example).

SELECT   P1.PAYMENTNO, P1.AMOUNT,
         (P1.AMOUNT * 100) / SUM(P2.AMOUNT)
FROM     PENALTIES AS P1, PENALTIES AS P2
GROUP BY P1.PAYMENTNO, P1.AMOUNT
ORDER BY P1.PAYMENTNO

The intermediate result of the FROM clause is equal to that of the previous example. However, the intermediate result of the GROUP BY clause differs:

P1.PAYNO  P1.AMOUNT  P2.PAYNO   P2.AMOUNT
--------  ---------  ---------  --------------
       1        100  {1, 2, 3}  {100, 75, 100}
       2         75  {1, 2, 3}  {100, 75, 100}
       3        100  {1, 2, 3}  {100, 75, 100}

The intermediate result of the SELECT clause is:

P1.PAYNO  P1.AMOUNT  (P1.AMOUNT * 100) / SUM(P2.AMOUNT)
--------  ---------  ----------------------------------
       1        100                               36.36
       2         75                               27.27
       3        100                               36.36

Determine yourself whether this is the final result.

Exercise 10.14: How many players live in a town, on average?

Exercise 10.15: For each team, get the team number, division, and number of players that played matches for that team.

Exercise 10.16: For each player, get the player number, name, sum of all penalties that he or she incurred, and number of teams from the first division that he or she captains.

Exercise 10.17: For each team captained by a player who lives in Stratford, get the team number and number of players who have won at least one match for that team.

Exercise 10.18: For each player, get the player number, name, and difference between the year in which he or she joined the club and the average year of joining the club.

Exercise 10.19: For each player, get the player number, name, and difference between the year in which he or she joined the club and the average year in which players who live in the same town joined the club.



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date