MySQL GROUP BY语句使用

Summary: in this tutorial, you will learn how to use MySQL GROUP BY to group rows into subgroups based on columns or values returned by an expression.

Introducing to MySQL GROUP BY clause

The MySQL GROUP BY clause is used with the SELECT statement to group rows into subgroups by the one or more values of columns or expressions.

The MySQL GROUP BY clause is an optional part of the SELECT statement. It must appear after the FROM or WHEREclause. The MySQL GROUP BY clause consists of the GROUP BY keyword followed by a list of comma-separated columns or expressions.

The following illustrates the MySQL GROUP BY clause syntax:

SELECT c1,c2,... cn, aggregate_function(expression) FROM table WHERE where_conditions GROUP BY c1, c2, ... cn

MySQL GROUP BY Examples

Let’s take a look at the orders table in the sample database. Suppose you want to group values of the order statusinto subgroups, you use the GROUP BY clause with the status column as the following query:

SELECT status FROM orders GROUP BY status

You can see that the GROUP BY clause returns unique occurrences of status values. It works like the DISTINCT operator as using in the following query:

SELECT DISTINCT status  FROM orders

MySQL GROUP BY with aggregate functions

The aggregate functions allow you to perform calculation of a set of records and return a single value. The most common aggregate functions are SUM, AVG, MAX, MIN and COUNT.

An aggregate functions is often used with the MySQL GROUP BY clause to perform calculation on each subgroup and return a single value for each subgroup. For example, if you want to know how many orders in each status, you can use the COUNT function with the GROUP BY clause as follows:

SELECT status, count(*) FROM orders GROUP BY status

MySQL GROUP BY vs. ANSI SQL GROUP BY

MySQL follows ANSI SQL. However, MySQL gives you more flexibility when using the GROUP BY clause:

• In ANSI SQL, you must list all columns that you use in the SELECT clause in the GROUP BY clause. MySQL does not have this restriction. MySQL allows you to have additional columns in the SELECT clause that are not specified in the GROUP BY clause.

• MySQL also allows you to sort the group order in which the results are returned. The default order is ascending.

If you want to see the status and the number of orders in descending order, you can use the GROUP BY clause with DESC as the following query:

SELECT status, count(*) FROM orders GROUP BY status DESC;

Notice that we use DESC in the GROUP BY clause to sort the status in descending order. You can specify ASCexplicitly in the GROUP BY clause to sort the groups in ascending order.

In this tutorial, we have shown you how to use the MySQL GROUP BY clause to group rows into subgroups based on columns or values returned from an expression.

Related Tutorials

• Using MySQL SELECT Statement to Query Data

• MySQL HAVING

原文链接：http://outofmemory.cn/mysql/mysql-group-by