MySQL GROUP BY Statement

The GROUP BY statement groups the result set by one or more columns.

On the grouped columns we can use functions such as COUNT, SUM, AVG, etc.

The GROUP BY statement is an important tool in SQL queries for summarizing and analyzing data. Especially when dealing with large amounts of data, it can provide useful summary information.

GROUP BY Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

• column1: specifies the column(s) to group by.
• aggregate_function(column2): aggregate function(s) executed on each group after grouping.
• table_name: the name of the table to query.
• condition: optional, the condition used to filter results.

Suppose there is a table named orders containing the following columns:order_id, customer_id, order_date, and order_amount。

We want to group by customer_id and calculate the total order amount for each customer. The SQL statement is as follows:

In the above example, we use GROUP BY customer_id to group the results by the customer_id column, then use SUM(order_amount) to calculate the sum of the order_amount column in each group.

AS total_amount is used to give an alias to the calculation result, making the query result easier to read.

Notes:

• GROUP BYThe clause is usually used together with aggregate functions, because after grouping, aggregate operations need to be performed on each group.
• SELECTColumns in the clause are usually either grouping columns or arguments of aggregate functions.
• Multiple columns can be used for grouping; just in theGROUP BYclause, separate the column names with commas.

Example Demonstration

The examples in this chapter use the following table structure and data. Before using them, we can first import the following data into the database.

After successful import, execute the following SQL statement:

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

Next, we use the GROUP BY statement to group the data table by name, and count how many records each person has:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

Using WITH ROLLUP

WITH ROLLUP can perform the same statistics (SUM, AVG, COUNT…) on the basis of grouped statistics.

For example, we group the above data table by name, and then count the number of logins for each person:

mysql> SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | signin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

The record with NULL represents the total number of logins for all people.

We can use coalesce to set a name that can replace NULL. The coalesce syntax is:

select coalesce(a,b,c);

Parameter description: if a==null, choose b; if b==null, choose c; if a!=null, choose a; if a, b, c are all null, then return null (meaningless).

In the following example, if the name is empty, we use the total instead:

mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)