Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - SQL SELECT Query Statements with GROUP BY

By: FYIcenter.com

Part:   1  2  3  4  5   6 

(Continued from previous part...)

How To Use Group Functions in the SELECT Clause?

If group functions are used in the SELECT clause, they will be used on the rows that meet the query selection criteria, the output of group functions will be returned as output of the query. The following select statement returns 3 values calculate by 3 group functions on all rows of the "fyi_links" table:

mysql> SELECT COUNT(*), MAX(counts), MIN(created) 
   FROM fyi_links;
+----------+-------------+---------------------+
| COUNT(*) | MAX(counts) | MIN(created)        |
+----------+-------------+---------------------+
|        7 |           8 | 2003-01-01 00:00:00 |
+----------+-------------+---------------------+
1 row in set (0.37 sec)

Can Group Functions Be Mixed with Non-group Selection Fields?

If a group function is used in the SELECT clause, all other selection fields must be group level fields. Non-group fields can not be mixed with group fields in the SELECT clause. The script below gives you an example of invalid SELECT statements with group and non-group selection fields:

mysql> SELECT COUNT(*), url FROM fyi_links;
ERROR 1140 (42000): Mixing of GROUP columns 
   (MIN(),MAX(),COUNT(),...) with no GROUP columns
   is illegal if there is no GROUP BY clause

In this example, COUNT(*) is a group field and "url" is a non-group field.

How To Divide Query Output into Groups?

You can divide query output into multiple groups with the GROUP BY clause. It allows you specify a column as the grouping criteria, so that rows with the same value in that column will be considered as a single group. When the GROUP BY clause is specified, the select statement can only be used to return group level information. The following script gives you a good GROUP BY example:

mysql> SELECT tag, COUNT(*), MIN(created), AVG(counts) 
   FROM fyi_links GROUP BY tag;
+------+----------+---------------------+-------------+
| tag  | COUNT(*) | MIN(created)        | AVG(counts) |
+------+----------+---------------------+-------------+
| DBA  |        3 | 2005-01-01 00:00:00 |      3.6667 |
| DEV  |        2 | 2004-01-01 00:00:00 |      4.0000 |
| SQA  |        2 | 2003-01-01 00:00:00 |      7.0000 |
+------+----------+---------------------+-------------+
3 rows in set (0.07 sec)

How To Apply Filtering Criteria at Group Level?

If you want to return only specific groups from the query, you can apply filtering criteria at the group level by using the HAVING clause inside the GROUP BY clause. Note group functions can also be used in HAVING conditions. The following tutorial exercise gives you some good HAVING example:

mysql> SELECT tag, COUNT(*), MIN(created), AVG(counts) 
   FROM fyi_links GROUP BY tag HAVING AVG(counts) > 3.8;
+------+----------+---------------------+-------------+
| tag  | COUNT(*) | MIN(created)        | AVG(counts) |
+------+----------+---------------------+-------------+
| DEV  |        2 | 2004-01-01 00:00:00 |      4.0000 |
| SQA  |        2 | 2003-01-01 00:00:00 |      7.0000 |
+------+----------+---------------------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT tag, COUNT(*), MIN(created), AVG(counts) 
   FROM fyi_links GROUP BY tag HAVING COUNT(*) > 2;
+------+----------+---------------------+-------------+
| tag  | COUNT(*) | MIN(created)        | AVG(counts) |
+------+----------+---------------------+-------------+
| DBA  |        3 | 2005-01-01 00:00:00 |      3.6667 |
+------+----------+---------------------+-------------+
1 row in set (0.00 sec)

(Continued on next part...)

Part:   1  2  3  4  5   6