{"id":212,"date":"2011-05-07T09:50:49","date_gmt":"2011-05-07T09:50:49","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=212"},"modified":"2024-06-09T21:04:05","modified_gmt":"2024-06-10T04:04:05","slug":"mysql-group-by","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-group-by\/","title":{"rendered":"MySQL GROUP BY"},"content":{"rendered":"\n

Summary:<\/strong> in this tutorial, you will learn how to use MySQL GROUP BY<\/code> to group rows into groups based on the values of columns or expressions.<\/p>\n\n\n\n

Introduction to MySQL GROUP BY clause<\/h2>\n\n\n\n

The GROUP BY<\/code> clause\u00a0groups rows into summary rows based on column values or expressions. It returns one row for each group and reduces the number of rows in the result set.<\/p>\n\n\n\n

The GROUP BY<\/code> clause is an optional part of the SELECT<\/a><\/code> statement. The following illustrates the syntax of the GROUP BY<\/code> clause:<\/p>\n\n\n

SELECT<\/span> \n    c1, c2,..., cn, aggregate_function(ci)\nFROM<\/span>\n    table_name\nWHERE<\/span>\n    conditions\nGROUP<\/span> BY<\/span> c1 , c2,...,cn;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you place the GROUP BY<\/code> clause after the FROM<\/code> and WHERE<\/code> clauses. Following the the GROUP BY<\/code> keywords, you list the columns or expressions you want to group, separated by commas.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In practice, you often use the GROUP BY<\/code> clause with aggregate functions<\/a> such as SUM<\/a><\/code>, AVG<\/a><\/code>, MAX<\/a><\/code>, MIN<\/a><\/code>, and COUNT<\/a><\/code>. The aggregate function that appears in the SELECT<\/code> clause provides the information for each group.<\/p>\n\n\n\n
MySQL GROUP BY examples<\/h2>\n\n\n\n
Let’s look at some examples of using the GROUP BY<\/code> clause.<\/p>\n\n\n\n
1) Basic MySQL GROUP BY example<\/h3>\n\n\n\n
We’ll use the orders<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If you want to group the order statuses, you can use the GROUP BY<\/code> clause with the status<\/code> column in the following query:<\/p>\n\n\n
SELECT<\/span> \n  status<\/span> \nFROM<\/span> \n  orders \nGROUP<\/span> BY<\/span> \n  status<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------+<\/span>\n| status     |\n+------------+<\/span>\n| Shipped    |\n| Resolved   |\n| Cancelled  |\n| On Hold    |\n| Disputed   |\n| In Process |\n+------------+<\/span>\n6 rows in set<\/span> (0.02<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows that the GROUP BY<\/code> clause returns unique occurrences of the values in the status<\/code> columns. <\/p>\n\n\n\n
SELECT<\/span> \n  DISTINCT<\/span> status<\/span> \nFROM<\/span> \n  orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using MySQL GROUP BY with aggregate functions<\/h3>\n\n\n\n
In practice, you often use the GROUP BY<\/code> clause with an aggregate function to group rows into sets and return a single value for each group.<\/p>\n\n\n\n
An aggregate function<\/a> calculates a set of rows and returns a single value.<\/p>\n\n\n\n
For example, to obtain the number of orders in each status, you can use the COUNT<\/code> function with the GROUP BY<\/code> clause as follows:<\/p>\n\n\n
SELECT<\/span> \n  status<\/span>, \n  COUNT<\/span>(*) \nFROM<\/span> \n  orders \nGROUP<\/span> BY<\/span> \n  status<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------------+----------+<\/span>\n| status     | COUNT(*) |\n+------------+----------+<\/span>\n| Shipped    |      303 |\n| Resolved   |        4 |\n| Cancelled  |        6 |\n| On Hold    |        4 |\n| Disputed   |        3 |\n| In Process |        6 |\n+------------+----------+<\/span>\n6 rows in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
See the following orders<\/code> and  orderdetails<\/code> table from the sample database:<\/p>\n\n\n\n
\"order-orderDetails-tables\"<\/figure>\n\n\n\n
To get the total amount of all orders by status, you join <\/a>the orders<\/code> table with the orderdetails<\/code> table and use the SUM<\/code> function to calculate the total amount:<\/p>\n\n\n
SELECT<\/span> \n  status<\/span>, \n  SUM<\/span>(quantityOrdered * priceEach) AS<\/span> amount \nFROM<\/span> \n  orders \n  INNER<\/span> JOIN<\/span> orderdetails USING<\/span> (orderNumber) \nGROUP<\/span> BY<\/span> \n  status<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------------+------------+<\/span>\n| status     | amount     |\n+------------+------------+<\/span>\n| Shipped    | 8865094.64 |\n| Resolved   |  134235.88 |\n| Cancelled  |  238854.18 |\n| On Hold    |  169575.61 |\n| Disputed   |   61158.78 |\n| In Process |  135271.52 |\n+------------+------------+<\/span>\n6 rows in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Similarly, the following query returns the order numbers and the total amount of each order.<\/p>\n\n\n
SELECT<\/span> \n  orderNumber, \n  SUM<\/span>(quantityOrdered * priceEach) AS<\/span> total \nFROM<\/span> \n  orderdetails \nGROUP<\/span> BY<\/span> \n  orderNumber;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
3) MySQL GROUP BY with expression example<\/h3>\n\n\n\n
In addition to columns, you can group rows by expressions. The following query calculates the total sales for each year:<\/p>\n\n\n
SELECT<\/span> \n  YEAR<\/span>(orderDate) AS<\/span> year<\/span>, \n  SUM<\/span>(quantityOrdered * priceEach) AS<\/span> total \nFROM<\/span> \n  orders \n  INNER<\/span> JOIN<\/span> orderdetails USING<\/span> (orderNumber) \nWHERE<\/span> \n  status<\/span> = 'Shipped'<\/span> \nGROUP<\/span> BY<\/span> \n  YEAR<\/span>(orderDate);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------+------------+<\/span>\n| year | total      |\n+------+------------+<\/span>\n| 2003 | 3223095.80 |\n| 2004 | 4300602.99 |\n| 2005 | 1341395.85 |\n+------+------------+<\/span>\n3 rows in set<\/span> (0.02<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the expression in the SELECT<\/code> clause must match the one in the GROUP BY<\/code> clause.<\/p>\n\n\n\n
4) Using MySQL GROUP BY with HAVING clause example<\/h3>\n\n\n\n
To filter the groups returned by GROUP BY<\/code> clause, you use a  HAVING<\/code><\/a> clause.<\/p>\n\n\n\n
The following query uses the HAVING<\/code> clause to select the total sales of the years after 2003.<\/p>\n\n\n
SELECT<\/span> \n  YEAR<\/span>(orderDate) AS<\/span> year<\/span>, \n  SUM<\/span>(quantityOrdered * priceEach) AS<\/span> total \nFROM<\/span> \n  orders \n  INNER<\/span> JOIN<\/span> orderdetails USING<\/span> (orderNumber) \nWHERE<\/span> \n  status<\/span> = 'Shipped'<\/span> \nGROUP<\/span> BY<\/span> \n  year<\/span> \nHAVING<\/span> \n  year<\/span> > 2003<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
5) Grouping by multiple columns<\/h3>\n\n\n\n
The following query returns the year, order status, and the total order for each combination of year and order status by grouping rows into groups:<\/p>\n\n\n
SELECT<\/span> \n  YEAR<\/span>(orderDate) AS<\/span> year<\/span>, \n  status<\/span>, \n  SUM<\/span>(quantityOrdered * priceEach) AS<\/span> total \nFROM<\/span> \n  orders \n  INNER<\/span> JOIN<\/span> orderdetails USING<\/span> (orderNumber) \nGROUP<\/span> BY<\/span> \n  year<\/span>, \n  status<\/span> \nORDER<\/span> BY<\/span> \n  year<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------+------------+------------+<\/span>\n| year | status     | total      |\n+------+------------+------------+<\/span>\n| 2003 | Cancelled  |   67130.69 |\n| 2003 | Resolved   |   27121.90 |\n| 2003 | Shipped    | 3223095.80 |\n| 2004 | Cancelled  |  171723.49 |\n| 2004 | On Hold    |   23014.17 |\n| 2004 | Resolved   |   20564.86 |\n| 2004 | Shipped    | 4300602.99 |\n| 2005 | Disputed   |   61158.78 |\n| 2005 | In Process |  135271.52 |\n| 2005 | On Hold    |  146561.44 |\n| 2005 | Resolved   |   86549.12 |\n| 2005 | Shipped    | 1341395.85 |\n+------+------------+------------+<\/span>\n12 rows in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The GROUP BY clause: MySQL vs. SQL standard<\/h2>\n\n\n\n
The SQL standard does not allow you to use an alias in the GROUP BY<\/code> clause whereas MySQL supports this.<\/p>\n\n\n\n
For example, the following query extracts the year from the order date. It first uses the year<\/code> as an alias of the expression YEAR(orderDate)<\/code> and then uses the year<\/code> alias in the GROUP BY<\/code> clause. <\/p>\n\n\n\n
The following query is not valid in SQL standard:<\/p>\n\n\n
SELECT<\/span> \n  YEAR<\/span>(orderDate) AS<\/span> year<\/span>, \n  COUNT<\/span>(orderNumber) \nFROM<\/span> \n  orders \nGROUP<\/span> BY<\/span> \n  year<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------+--------------------+<\/span>\n| year | COUNT(orderNumber) |\n+------+--------------------+<\/span>\n| 2003 |                111 |\n| 2004 |                151 |\n| 2005 |                 64 |\n+------+--------------------+<\/span>\n3 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The GROUP BY clause vs. DISTINCT clause<\/h2>\n\n\n\n
If you use the GROUP BY<\/code> clause in the SELECT<\/code> statement without using aggregate functions<\/a>, the GROUP BY<\/code> clause behaves like the DISTINCT<\/a><\/code> clause.<\/p>\n\n\n\n
The following statement uses the GROUP BY<\/code> clause to select the unique states of customers from the customers<\/code> table.<\/p>\n\n\n
SELECT<\/span> \n  state \nFROM<\/span> \n  customers \nGROUP<\/span> BY<\/span> \n  state;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------+<\/span>\n| state         |\n+---------------+<\/span>\n| NULL          |\n| NV            |\n| Victoria      |\n| CA            |\n| NY            |\n| PA            |\n| CT            |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can achieve a similar result by using the DISTINCT<\/code> clause:<\/p>\n\n\n
SELECT<\/span> \n  DISTINCT<\/span> state \nFROM<\/span> \n  customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------+<\/span>\n| state         |\n+---------------+<\/span>\n| NULL          |\n| NV            |\n| Victoria      |\n| CA            |\n| NY            |\n| PA            |\n| CT            |\n...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that MySQL 8.0 or later removed the implicit sorting for the GROUP BY<\/code> clause. Therefore, if you are using earlier versions, you will find that the result set with the GROUP BY<\/code> clause is sorted.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the GROUP BY<\/code> clause to group rows into subgroups.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
In this example, we used the YEAR<\/a><\/code> function to extract year data from order date ( orderDate<\/code>) and included only orders with shipped<\/code> status in the total sales. <\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
It works like the DISTINCT<\/a><\/code> operator, as demonstrated in the following query:<\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
MySQL evaluates the GROUP BY<\/a><\/code> clause after the FROM<\/code> and WHERE<\/a><\/code> clauses but before the HAVING<\/a><\/code>, SELECT<\/code>, DISTINCT<\/a><\/code>, ORDER BY<\/a><\/code> and LIMIT<\/a><\/code> clauses:<\/p>\n\n\n\n