Mysql Group By Statement

MySQL GROUP BY Statement \n\n

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

\n\n

It is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

\n\n

The GROUP BY statement is an important tool in SQL queries for summarizing and analyzing data, especially when dealing with large datasets, as it provides useful summary information.

\n\n

GROUP BY Syntax

\n\n

SELECT column_name(s)\nFROM table_name\nWHERE condition\nGROUP BY column_name(s);

\n\n

column_name(s): Specifies the column(s) to group by.
aggregate_function(column_name): An aggregate function applied to each group.
table_name: The name of the table to query.
condition: Optional. A condition to filter the results.

\n\n

Assume there is a table named orders with the following columns: order_id, customer_id, order_date, and order_amount.

\n\n

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

\n\n

Example

\n\n

SELECT customer_id, SUM(order_amount) AS total_amount\nFROM orders\nGROUP BY customer_id;

\n\n

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

\n\n

AS total_amount is used to give an alias to the calculated result, making the query result more readable.

\n\n

Notes:

\n\n

The GROUP BY clause is often used with aggregate functions because grouping requires performing an aggregate operation on each group.
Columns in the SELECT clause are typically either the grouping columns or arguments to aggregate functions.
You can group by multiple columns by separating the column names with commas in the GROUP BY clause.

\n\n

Example

\n\n

SELECT column1, column2, aggregate_function(column3)\nFROM TABLE_NAME\nWHERE condition\nGROUP BY column1, column2;

\n\n

Practical Examples

\n\n

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

\n\n

Example

\n\n

SET NAMES utf8;\n\nSET FOREIGN_KEY_CHECKS = 0;\n\n-- ----------------------------\n-- Table structure for `employee_tbl`\n-- ----------------------------\nDROP TABLE IF EXISTS `employee_tbl`;\nCREATE TABLE `employee_tbl` (\n  `id` int(11) NOT NULL,\n  `name` char(10) NOT NULL DEFAULT '',\n  `date` datetime NOT NULL,\n  `signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Login Times',\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\n-- ----------------------------\n-- Records of `employee_tbl`\n-- ----------------------------\nBEGIN;\nINSERT INTO `employee_tbl` VALUES ('1', 'Xiao Ming', '2016-04-22 15:25:33', '1'), ('2', 'Xiao Wang', '2016-04-20 15:25:47', '3'), ('3', 'Xiao Li', '2016-04-19 15:26:02', '2'), ('4', 'Xiao Wang', '2016-04-07 15:26:14', '4'), ('5', 'Xiao Ming', '2016-04-11 15:26:40', '4'), ('6', 'Xiao Ming', '2016-04-04 15:26:54', '2');\nCOMMIT;\n\nSET FOREIGN_KEY_CHECKS = 1;

\n\n

After successful import, execute the following SQL statement:

\n\n

mysql> set names utf8;\nmysql> SELECT * FROM employee_tbl;\n+----+--------+---------------------+--------+\n| id | name   | date                | signin |\n+----+--------+---------------------+--------+\n|  1 | Xiao Ming   | 2016-04-22 15:25:33 |      1 |\n|  2 | Xiao Wang   | 2016-04-20 15:25:47 |      3 |\n|  3 | Xiao Li   | 2016-04-19 15:26:02 |      2 |\n|  4 | Xiao Wang   | 2016-04-07 15:26:14 |      4 |\n|  5 | Xiao Ming   | 2016-04-11 15:26:40 |      4 |\n|  6 | Xiao Ming   | 2016-04-04 15:26:54 |      2 |\n+----+--------+---------------------+--------+\n6 rows in set (0.00 sec)

\n\n

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

\n\n

mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;\n+--------+----------+\n| name   | COUNT(*) |\n+--------+----------+\n| Xiao Li   |        1 |\n| Xiao Ming   |        3 |\n| Xiao Wang   |        2 |\n+--------+----------+\n3 rows in set (0.01 sec)

\n\n

Using WITH ROLLUP

\n\n

WITH ROLLUP can be used to perform the same aggregation (SUM, AVG, COUNT, etc.) on the grouped summary data.

\n\n

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

\n\n

mysql> SELECT name, SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;\n+--------+--------------+\n| name   | signin_count |\n+--------+--------------+\n| Xiao Li   |            2 |\n| Xiao Ming   |            7 |\n| Xiao Wang   |            7 |\n| NULL   |           16 |\n+--------+--------------+\n4 rows in set (0.00 sec)

\n\n

The record with NULL represents the total sign-in count for all people.

\n\n

We can use COALESCE to set a name to replace NULL. The syntax for COALESCE is:

\n\n

SELECT COALESCE(a, b, c);

\n\n

Parameter description: If a is NULL, then b is chosen; if b is NULL, then c is chosen; if a is not NULL, then a is chosen; if a, b, and c are all NULL, then NULL is returned (which is meaningless).

\n\n

In the following example, if the name is NULL, we use 'Total' instead:

\n\n

mysql> SELECT COALESCE(name, 'Total'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;\n+--------------------------+--------------+\n| COALESCE(name, 'Total')  | signin_count |\n+--------------------------+--------------+\n| Xiao Li                     |            2 |\n| Xiao Ming                     |            7 |\n| Xiao Wang                     |            7 |\n| Total                    |           16 |\n+--------------------------+--------------+\n4 rows in set (0.01 sec)

YouTip