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 2

By DatabaseJournal.com Staff

10.3 Grouping on Two or More Columns

A GROUP BY clause can contain two or more columns—or, in other words, a grouping can consist of two or more columns. The next two examples illustrate this topic.

Example 10.6: For the MATCHES table, get all the different combinations of team numbers and player numbers.

SELECT   TEAMNO, PLAYERNO
FROM     MATCHES
GROUP BY TEAMNO, PLAYERNO

The result is grouped not on one column, but on two. All rows with the same team number and the same player number form a group.

The intermediate result from the GROUP BY clause is:

TEAMNO  PLAYERNO  MATCHNO    WON        LOST
------  --------  ---------  ---------  ---------
     1         2  {6}        {1}        {3}
     1         6  {1, 2, 3}  {3, 2, 3}  {1, 3, 0}
     1         8  {8}        {0}        {3}
     1        44  {4}        {3}        {2}
     1        57  {7}        {3}        {0}
     1        83  {5}        {0}        {3}
     2         8  {13}       {0}        {3}
     2        27  {9}        {3}        {2}
     2       104  {10}       {3}        {2}
     2       112  {11, 12}   {2, 1}     {3, 3}

The end result is:

TEAMNO  PLAYERNO
------  --------
     1         2
     1         6
     1         8
     1        44
     1        57
     1        83
     2         8
     2        27
     2       104
     2       112

The sequence of the columns in the GROUP BY clause has no effect on the end result of a statement. The following statement, therefore, is equivalent to the previous one:

SELECT   TEAMNO, PLAYERNO
FROM     MATCHES
GROUP BY PLAYERNO, TEAMNO

As an example, let us add some aggregation functions to the previous SELECT statement:

SELECT   TEAMNO, PLAYERNO, SUM(WON),
         COUNT(*), MIN(LOST)
FROM     MATCHES
GROUP BY TEAMNO, PLAYERNO

The result is:

TEAMNO  PLAYERNO  SUM(WON)  COUNT(*)  MIN(LOST)
------  --------  --------  --------  ---------
     1         2         1         1          3
     1         6         8         3          0
     1         8         0         1          3
     1        44         3         1          2
     1        57         3         1          0
     1        83         0         1          3
     2         8         0         1          3
     2        27         3         1          2
     2       104         3         1          2
     2       112         3         2          3

In this example, the grouping is equal to [TEAMNO, PLAYERNO], and the aggregation level of the result is the combination of team number and player number. This aggregation level is lower than that of a statement in which the grouping is equal to [TEAMNO] or [TOWN].

Example 10.7: For each player who has ever incurred at least one penalty, get the player number, name, and total amount of penalties incurred.

SELECT   P.PLAYERNO, NAME, SUM(AMOUNT)
FROM     PLAYERS AS P INNER JOIN PENALTIES AS PEN
         ON P.PLAYERNO = PEN.PLAYERNO
GROUP BY P.PLAYERNO, NAME

The result is:

P.PLAYERNO  NAME       SUM(AMOUNT)
----------  ---------  -----------
         6  Parmenter       100.00
         8  Newcastle        25.00
        27  Collins         175.00
        44  Baker           130.00
       104  Moorman          50.00

Explanation: This example also has a grouping consisting of two columns. The statement would have given the same result if the PEN.PLAYERNO column had also been added to the grouping. Work this out by yourself.

Exercise 10.5: For each combination of won-lost sets in the MATCHES table, get the number of matches won.

Exercise 10.6: Group the matches on town of player and division of team, and get the sum of the number of sets won for each combination of town-division.

Exercise 10.7: For each player who lives in Inglewood, get the name, initials, and number of penalties incurred by him or her.

Exercise 10.8: For each team, get the team number, division, and total number of sets won.

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.

10.5 Grouping of Null Values

If grouping is required on a column that contains null values, all these null values form one group because a GROUP BY clause applies a vertical comparison. This is in accordance with the rules described in Section 9.5.

Example 10.10: Find the different league numbers.

SELECT   LEAGUENO
FROM     PLAYERS
GROUP BY LEAGUENO

The result is:

LEAGUENO
--------
1124
1319
1608
2411
2513
2983
6409
6524
7060
8467
?

Explanation: Players 7, 28, 39, and 95 do not have a league number and, therefore, form one group (the last row) in the end result.



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