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 3

By DatabaseJournal.com Staff

10.6 Grouping with Sorting

In many cases, a select block containing a GROUP BY clause ends with an ORDER BY clause. And many times the columns specified in that ORDER BY clause are the same as the ones specified in the GROUP BY clause. These statements can be simplified by combining the two clauses.

Example 10.11: For each team, get the number of matches and sort the result in descending order by team number.

The obvious formulation is:

SELECT   TEAMNO, COUNT(*)
FROM     MATCHES
GROUP BY TEAMNO
ORDER BY TEAMNO DESC

The result is:

TEAMNO  COUNT(*)
------  --------
     2         5
     1         8

Explanation: The specification DESC is a sort direction and indicates that the result must be sorted in a descending order. This statement can be simplified by including the specification DESC in the GROUP BY clause.

If the result must have an ascending sort direction, ASC (ascending) must be specified.

10.7 General Rules for the GROUP BY Clause

This section describes a number of important rules for select blocks with a GROUP BY clause.

Rule 1: Section 9.7 gives several rules for the use of aggregation functions in the SELECT clause. For many SQL products, the following rule applies: If a select block has a GROUP BY clause, any column specification in the SELECT clause must exclusively occur as a parameter of an aggregation function, or in the list of columns given in the GROUP BY clause, or in both. Therefore, for most products, the following statement is incorrect because the TOWN column appears in the SELECT clause, yet it is not the parameter of an aggregation function and does not occur in the list of columns by which the result is grouped.

SELECT   TOWN, COUNT(*)
FROM     PLAYERS
GROUP BY SEX

This restriction is because the result of an aggregation function always consists of one value for each group. The result of a column specification on which grouping is performed also always consists of one value per group. These results are compatible. In contrast, the result of a column specification on which no grouping is performed consists of a set of values. This would not be compatible with the results of the other expressions in the SELECT clause.

This rule does not apply for MySQL. The previous query will return the following result:

TOWN       COUNT(*)
---------  --------
Stratford         9
Inglewood         5

The value of the second column is understandable—it is the number of players per sex. But the answer of the first column is unexpected. Why does it show Stratford in the first row and Inglewood in the second? This is strange because, for each sex, there can be multiple towns. The answer is that MySQL itself determines the values to be returned. Those values are selected almost randomly. We can enforce this rule if we add the setting ONLY_FULL_GROUP_BY to the SQL_MODE system variable.

Therefore, we strongly recommend that you do not formulate this type of SQL statement, and instead adhere to this rule that applies to most SQL products.

Rule 2: In most examples, the expressions used to form groups also occur in the SELECT clause. However, that is not necessary. An expression that occurs in the GROUP BY clause can appear in the SELECT clause.

Rule 3: An expression that is used to form groups can also occur in the SELECT clause within a compound expression. See the next example.

Example 10.12: Get the list with the different penalty amounts in cents.

SELECT   CAST(AMOUNT * 100 AS SIGNED INTEGER) 
         AS AMOUNT_IN_CENTS
FROM     PENALTIES
GROUP BY AMOUNT

The result is:

AMOUNT_IN_CENTS
---------------
           2500
           3000
           5000
           7500
          10000

Explanation: A grouping is performed on a simple expression consisting of the column name AMOUNT. In the SELECT clause, that same AMOUNT column occurs within a compound expression. This is allowed.

No matter how complex a compound expression is, if it occurs in a GROUP BY clause, it can be included in its entirety only in the SELECT clause. For example, if the compound expression PLAYERNO * 2 occurs in a GROUP BY clause, the expressions PLAYERNO * 2, (PLAYERNO * 2) – 100 and MOD(PLAYERNO * 2, 3) – 100 can occur in the SELECT clause. On the other hand, the expressions PLAYERNO, 2 * PLAYERNO, PLAYERNO * 100, and 8 * PLAYERNO * 2 are not allowed.

Rule 4: If an expression occurs more than once in a GROUP BY clause, double expressions are simply removed. The GROUP BY clause GROUP BY TOWN, TOWN is converted to GROUP BY TOWN. Also GROUP BY SUBSTR(TOWN,1,1), SEX, SUBSTR(TOWN,1,1) is converted to GROUP BY SUBSTR(TOWN,1,1), SEX.

Rule 5: Section 9.4 described the cases in which the use of DISTINCT in the SELECT clause is superfluous. The rules given in that section apply to SELECT statements without a GROUP BY clause. A different rule exists for SELECT statements with a GROUP BY clause: DISTINCT (if used outside an aggregation function) is superfluous when the SELECT clause includes all the columns specified in the GROUP BY clause. The GROUP BY clause groups the rows in such a way that the column(s) on which they are grouped no longer contain duplicate values.

Exercise 10.12: Describe why the following statements are incorrect:

  1. SELECT  PLAYERNO, DIVISION
    FROM    TEAMS
    GROUP BY PLAYERNO
  2. SELECT  SUBSTR(TOWN,1,1), NAME
    FROM    PLAYERS
    GROUP BY TOWN, SUBSTR(NAME,1,1)
  3. SELECT  PLAYERNO * (AMOUNT + 100)
    FROM    PENALTIES
    GROUP BY AMOUNT + 100

Exercise 10.13: In which of the following statements is DISTINCT superfluous?

  1. SELECT DISTINCT PLAYERNO
    FROM   TEAMS
    GROUP BY PLAYERNO
  2. SELECT  DISTINCT COUNT(*)
    FROM    MATCHES
    GROUP BY TEAMNO
  3. SELECT  DISTINCT COUNT(*)
    FROM    MATCHES
    WHERE   TEAMNO = 2
    GROUP BY TEAMNO

10.8 The GROUP_CONCAT Function

A special aggregation function that MySQL supports is the GROUP_CONCAT function. The value of this function is equal to all values of the specified column belonging to a group. These values are placed behind each other, separated by commas, and are presented as one long alphanumeric value.

Example 10.13: For each team, get the team number and list of players who played matches for that team.

SELECT   TEAMNO, GROUP_CONCAT(PLAYERNO)
FROM     MATCHES
GROUP BY TEAMNO

The result is:

TEAMNO  GROUP_CONCAT(PLAYERNO)
------  ----------------------
     1  6,8,57,2,83,44,6,6
     2  27,104,112,112,8

The GROUP_CONCAT function can also be used on the column on which the result is grouped.

Example 10.14: For each team, get the team number and for each player, who played matches for that team, get that same team number.

SELECT   TEAMNO, GROUP_CONCAT(TEAMNO)
FROM     MATCHES
GROUP BY TEAMNO

The result is:

TEAMNO  GROUP_CONCAT(TEAMNO)
------  --------------------
     1  1,1,1,1,1,1,1,1
     2  2,2,2,2,2

If a select block contains no GROUP BY clause, the GROUP_CONCAT function is processed on all the values of a column.

Example 10.15: Get all the payment numbers.

SELECT   GROUP_CONCAT(PAYMENTNO)
FROM     PENALTIES

The result is:

GROUP_CONCAT(BETALINGSNR)
-------------------------
1,2,3,4,5,6,7,8

The length of the alphanumeric value of a GROUP_CONCAT function is restricted. The system variable GROUP_CONCAT_MAX_LEN indicates the maximum length. This variable has a standard value of 1,024 and can be adjusted with a SET statement.

Example 10.16: Reduce the length of the GROUP_CONCAT function to seven characters and execute the statement of the previous example.

SET @@GROUP_CONCAT_MAX_LEN=7
SELECT TEAMNO, GROUP_CONCAT(TEAMNO) FROM MATCHES GROUP BY TEAMNO

The result is:

TEAMNO  GROUP_CONCAT(TEAMNO)
------  --------------------
     1  1,1,1,1
     2  2,2,2,2


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