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 Count Duplicated Values in a Column?

If you have a column with duplicated values, and you want to know what are those duplicated values are and how many duplicates are there for each of those values, you can use the GROUP BY ... HAVING clause as shown in the following example. It returns how many duplicated first names in the fyi_team table:

mysql> SELECT first_name, COUNT(*) FROM fyi_team 
   GROUP BY first_name HAVING COUNT(*) > 1;
+------------+----------+
| first_name | COUNT(*) |
+------------+----------+
| John       |        6 |
+------------+----------+
1 row in set (0.01 sec)

Can Multiple Columns Be Used in GROUP BY?

If you want to break your output into smaller groups, if you specify multiple column names or expressions in the GROUP BY clause. Output in each group must satisfy a specific combination of the expressions listed in the GROUP BY clause. The more columns or expressions entered in the GROUP BY clause, the smaller the groups will be.

The tutorial exercise below shows you how to break data into groups per "tag" and per year when they were created. Then the group function COUNT(*) is applied on each group:

mysql> SELECT tag, YEAR(created), COUNT(*) 
   FROM fyi_links GROUP BY tag, YEAR(created);
+------+---------------+----------+
| tag  | YEAR(created) | COUNT(*) |
+------+---------------+----------+
| DBA  |          2005 |        1 |
| DBA  |          2006 |        2 |
| DEV  |          2004 |        1 |
| DEV  |          2006 |        1 |
| SQA  |          2003 |        1 |
| SQA  |          2006 |        1 |
+------+---------------+----------+
6 rows in set (0.00 sec)

Can Group Functions Be Used in the ORDER BY Clause?

If the query output is aggregated as groups, you can sort the groups by using group functions in the ORDER BY clause. The following statement returns how many links were created in each year in each tag. The group output is sorted by the count in each group in descending order:

mysql> SELECT tag, YEAR(created), COUNT(*) 
   FROM fyi_links GROUP BY tag, YEAR(created) 
   ORDER BY COUNT(*) DESC;
+------+---------------+----------+
| tag  | YEAR(created) | COUNT(*) |
+------+---------------+----------+
| DBA  |          2006 |        2 |
| DEV  |          2006 |        1 |
| SQA  |          2006 |        1 |
| DBA  |          2005 |        1 |
| DEV  |          2004 |        1 |
| SQA  |          2003 |        1 |
+------+---------------+----------+
6 rows in set (0.00 sec)

Part:   1  2  3  4  5  6