MySQL GROUP BY Clause – Tutorial With Examples

Learn about MySQL GROUP BY command to group data of the table as per the different operations performed on the table with examples:

The MySQL GROUP BY clause is used as an optional clause along with the SELECT command and is used to summarize the data in table rows.

The GROUP BY clause is an important clause used for combining data based on a column value and perform different operations like COUNT, SUM, AVERAGE, etc.

=> Click here for the complete MySQL tutorial series

MySQL GROUP BYMySQL GROUP BY

MySQL GROUP BY

It has a lot of important use cases while analyzing data between one or multiple tables (queried using MySQL JOINS) – For example, determining total sales in a given month/quarter/year, etc using GROUP BY different parts of existing columns, etc.

Syntax:

SELECT column1, column2, column3
FROM tableName
GROUP BY column1, [column2]
[HAVING condition]

Let’s try to understand this syntax

  • SELECT: It is a normal select query that specifies different columns that need to be queried from the given table(s).
  • GROUP BY: This requires one (or multiple) column names based on which we want the results to be aggregated.
  • [HAVING condition]: This is an optional condition that could be specified for SELECT queries that use the GROUP BY clause. It’s similar to a WHERE clause in a normal SELECT query.

Sample Data:

Let’s consider that we have an ORDERS table having details of customer orders that are placed across different stores.

We will be creating 3 tables with the following fields:

#1) ORDERS

  • order_id – INT
  • customer_name – VARCHAR
  • city – VARCHAR
  • order_total – DECIMAL
  • date – DATETIME

#2) ORDER_DETAILS

  • order_id – INT (Referenced from orders -> order_id)
  • product_id – INT (Referenced from product_details -> product_id)
  • quantity – INT

#3) PRODUCT_DETAILS

  • product_id – INT
  • product_name – VARCHAR

Let’s create these tables and insert sample data into these tables:

CREATE TABLE `orders` (
  `order_id` INT NOT NULL,
  `customer_name` VARCHAR(255),
  `city` VARCHAR(255),
  `order_total` DECIMAL(5,2),
  `order_date` VARCHAR(255),
  PRIMARY KEY (order_id)
) 

CREATE TABLE `order_details` (
  `order_id` INT,
  `product_id` INT,
  `quantity` INT,
  FOREIGN KEY (product_id) REFERENCES product_details(product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
)

CREATE TABLE `product_details` (
  `product_id` INT NOT NULL,
  `product_name` VARCHAR(100),
  PRIMARY KEY(product_id));

------------------------------------------------------------------------
// insert data into orders table
------------------------------------------------------------------------
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1000,"Roth W. Porter","Köln","117.79","2020-12-28 12:23:13"),(1001,"Abbot G. Mann","Scena/Schenna","95.73","2020-06-14 15:39:50"),(1002,"Christian Park","Wyoming","37.28","2021-06-23 05:18:13"),(1003,"Florence Pace","Surendranagar","95.55","2020-11-05 11:17:50"),(1004,"Calvin Mathis","Belfast","115.85","2021-10-06 22:23:25"),(1005,"Portia X. Sutton","Götzis","123.70","2020-06-11 17:50:17"),(1006,"Arden Q. Morton","Rostock","88.20","2021-07-02 03:11:57"),(1007,"Felix Hurley","Bocchigliero","52.68","2020-05-05 22:43:22"),(1008,"Hilary F. Cortez","San Rosendo","124.01","2020-09-10 17:55:19"),(1009,"Jordan Farrell","Essex","58.99","2020-08-28 18:44:00");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1010,"Whilemina G. Morris","Casole d'Elsa","45.74","2021-06-14 04:09:53"),(1011,"Malik E. Mcclure","Tambov","50.40","2020-08-26 10:04:10"),(1012,"Adam Keller","St. Paul","113.78","2019-11-26 17:25:13"),(1013,"Nero Curtis","Gliwice","91.24","2020-10-15 03:22:04"),(1014,"Axel W. Bruce","Minneapolis","60.28","2020-05-25 13:20:57"),(1015,"Buffy G. Martinez","Arsoli","59.25","2021-01-22 19:04:20"),(1016,"Blaze Fox","Hastings","59.61","2020-07-14 18:23:00"),(1017,"Cameran Velasquez","Saint-Dié-des-Vosges","115.37","2021-04-04 22:29:55"),(1018,"Janna Solis","Molina","41.57","2020-05-27 08:44:09"),(1019,"Gil K. Rivera","Tranås","111.41","2020-11-14 00:57:47");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1020,"Adena Y. Barnett","Mal","43.95","2019-11-17 16:00:21"),(1021,"Idola Tyler","Ingraj Bazar","41.42","2021-01-29 16:16:10"),(1022,"Josephine Gilliam","Berwick","97.35","2020-12-27 09:27:30"),(1023,"Jenna Whitaker","Gatchina","109.41","2020-08-01 00:04:40"),(1024,"Damon Norman","Saint-Marc","60.72","2021-02-19 04:49:02"),(1025,"Meredith Maldonado","Libramont-Chevigny","25.04","2020-10-31 06:06:15"),(1026,"India X. Hurley","Bottidda","55.93","2021-10-14 12:23:01"),(1027,"Chantale R. Baird","Niort","111.73","2020-07-21 15:54:08"),(1028,"Chanda Stafford","Magdeburg","111.64","2021-01-08 16:52:49"),(1029,"India Tate","Yangju","62.27","2021-07-29 11:29:52");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1030,"Ryder Q. Clayton","Bad Nauheim","73.42","2021-02-20 04:45:11"),(1031,"Griffin I. Ramos","Kech","42.63","2021-10-04 23:49:57"),(1032,"Wang H. Saunders","Fusagasugá","65.72","2020-04-20 21:23:11"),(1033,"Juliet K. Morse","Pugwash","105.93","2020-05-01 07:06:23"),(1034,"Heather T. Mcmahon","Altamura","28.44","2021-03-09 03:39:44"),(1035,"Shannon S. Odonnell","Vandoies/Vintl","45.39","2021-09-10 23:48:49"),(1036,"Jacob Torres","San Giacomo degli Schiavoni","96.92","2020-12-12 09:17:43"),(1037,"Clayton L. Garrison","Watson Lake","76.72","2020-11-25 10:55:18"),(1038,"Naida S. Douglas","Bergisch Gladbach","66.65","2021-08-30 03:46:09"),(1039,"Adara W. Bernard","Alma","104.99","2020-01-19 13:43:09");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1040,"Aphrodite J. Carrillo","Banda","74.73","2021-09-12 03:32:31"),(1041,"Cameron Hendricks","Pilibhit","83.08","2019-12-26 17:44:22"),(1042,"Dale J. Marsh","OugrŽe","60.17","2021-02-19 06:04:12"),(1043,"Plato Jimenez","Aubagne","62.75","2020-10-24 09:14:01"),(1044,"Vincent F. Burris","Drogenbos","62.18","2021-10-29 03:08:20"),(1045,"Reed Jordan","Uppingham. Cottesmore","66.00","2021-02-11 11:01:36"),(1046,"Giacomo Paul","Cumaribo","115.53","2020-12-27 18:45:14"),(1047,"Kuame A. Norman","Darlington","54.62","2020-05-23 11:07:50"),(1048,"Simon Cotton","Losino-Petrovsky","113.52","2021-09-27 05:54:02"),(1049,"Camden V. Ramos","Dutse","79.21","2021-10-13 02:39:30");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1050,"Samson Willis","Puerto Vallarta","27.80","2021-01-25 05:05:52"),(1051,"Regina N. Raymond","Pescantina","27.32","2020-01-11 06:51:09"),(1052,"Constance Schwartz","Blieskastel","73.52","2020-12-30 16:47:53"),(1053,"Hamilton Church","Ercis","66.38","2020-11-08 04:56:46"),(1054,"Aidan T. Davenport","Tonalá","37.83","2019-12-04 13:15:41"),(1055,"Suki Rasmussen","Dyatkovo","105.50","2020-11-06 22:26:25"),(1056,"Armando Hoover","Marano Lagunare","88.68","2021-01-08 05:21:47"),(1057,"Baker Oliver","Mandi Burewala","116.19","2021-09-23 09:12:28"),(1058,"Tana Le","Crystal Springs","85.09","2021-02-08 01:26:23"),(1059,"Finn Dejesus","Swan Hill","68.85","2021-05-10 04:57:17");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1060,"Shafira Cleveland","El Quisco","103.65","2020-04-13 19:18:25"),(1061,"Hector C. Henson","Singkawang","87.34","2021-06-16 04:01:45"),(1062,"Donna X. Craig","Graven Grez-Doiceau","32.79","2020-07-10 04:39:11"),(1063,"Kenyon P. Wilson","Lives-sur-Meuse","38.66","2021-06-18 06:38:19"),(1064,"Harlan R. Wagner","Lipetsk","35.57","2021-02-24 21:18:25"),(1065,"Belle Hampton","Parla","69.93","2021-06-03 02:04:57"),(1066,"Sean K. Ayers","Morpeth","68.03","2021-06-17 04:01:53"),(1067,"Montana Randall","Sautin","68.99","2020-04-04 23:45:47"),(1068,"Kelsie J. Bond","Cherepovets","99.28","2021-11-07 23:07:55"),(1069,"Hashim Y. Burch","Villarrica","57.76","2020-02-16 18:02:08");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1070,"Adria F. Kline","Sunshine Coast Regional District","110.53","2021-01-15 14:58:55"),(1071,"Aline Bryant","Cumberland","74.46","2020-06-07 02:10:17"),(1072,"Shoshana F. Patterson","Tczew","80.63","2020-06-15 14:40:21"),(1073,"Ferris Casey","Baschi","31.61","2021-04-16 02:12:38"),(1074,"Brianna Vargas","Khanewal","113.46","2020-11-07 10:59:43"),(1075,"Beatrice C. Fleming","San Ignacio","75.02","2020-06-10 10:35:10"),(1076,"Teagan W. Bush","Laval","38.63","2020-02-03 05:39:34"),(1077,"Audra G. Harmon","Legal","41.97","2020-05-07 00:15:01"),(1078,"Aaron Maldonado","Montauban","92.13","2020-08-01 16:27:49"),(1079,"Amir Hurst","Villers-le-Peuplier","93.73","2020-12-29 06:32:11");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1080,"Nell L. Aguirre","Hanam","109.31","2021-04-11 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2021-05-24 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2021-07-24 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis Contreras","Montluçon","28.15","2021-04-10 05:56:04"),(1088,"Felix Q. Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell Willis","Quarona","101.34","2020-02-03 02:05:00");
INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1090,"Austin T. Casey","Cardiff","108.22","2021-05-06 11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2021-07-09 18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2021-01-16 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2021-09-23 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2021-07-25 01:10:52"),(1098,"Ina Rush","Herne","84.31","2021-08-17 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2021-09-06 12:48:52");

-------------------------------------------------------------------------------
// insert data into order_details table
-------------------------------------------------------------------------------

INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1089,7,3),(1024,5,1),(1067,5,3),(1089,6,3),(1027,3,4),(1054,4,1),(1070,6,4),(1026,1,3),(1039,8,2),(1009,8,4);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1016,3,3),(1051,7,4),(1095,6,4),(1098,4,2),(1052,2,4),(1064,1,4),(1052,7,4),(1004,1,4),(1099,5,2),(1075,9,1);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1069,5,3),(1071,2,2),(1088,5,3),(1090,7,1),(1021,1,4),(1076,5,4),(1060,5,1),(1029,10,1),(1035,9,4),(1095,10,2);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1045,1,2),(1043,6,3),(1073,5,2),(1082,1,1),(1050,5,1),(1057,3,4),(1062,2,3),(1029,1,1),(1020,9,3),(1003,3,1);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1074,2,2),(1025,1,2),(1025,6,4),(1083,7,1),(1072,6,2),(1045,9,4),(1098,9,2),(1023,5,2),(1098,6,3),(1052,10,4);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1066,2,2),(1018,9,3),(1061,3,3),(1007,2,2),(1093,9,1),(1035,3,2),(1090,3,4),(1021,9,2),(1095,9,1),(1004,1,4);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1006,6,3),(1033,5,4),(1022,7,4),(1051,1,2),(1000,7,2),(1017,9,4),(1016,5,3),(1083,10,2),(1018,1,1),(1018,3,4);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1039,1,3),(1051,4,3),(1024,6,4),(1048,10,4),(1091,2,4),(1023,9,2),(1065,8,4),(1098,10,2),(1018,8,1),(1043,8,3);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1084,6,1),(1009,8,3),(1015,4,3),(1041,7,1),(1046,7,4),(1071,6,1),(1023,3,3),(1031,7,4),(1085,9,1),(1014,2,2);
INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1047,2,3),(1012,9,2),(1079,2,3),(1019,3,1),(1021,5,3),(1003,5,2),(1088,3,2),(1091,3,3),(1048,4,3),(1060,9,2);
-------------------------------------------------------------------------------
// insert data into product_details table
-------------------------------------------------------------------------------
INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (1,'Biscuits'),(2,'Chocolates'), (3, 'Washing Powder'),(4, 'Flour'), (5, 'Batteries'),(6,'Floor cleaner'),(7,'Toothbrush'),(8,'Fruits'),(9,'Vegetables'),(10,'Sugar');

Please refer to images of the tables with the test data as created above.

PRODUCT_DETAILS:

product_details

product_details

ORDERS

ordersorders

ORDER_DETAILS

order_detailsorder_details

GROUP BY Examples

In this section, we will see different ways in which we can use GROUP_BY clauses to aggregate information at different levels.

Note – Displaying limited row output (where no of rows are > 20 to avoid a lot of output from tables)

Simple GROUP BY

Let’s see some examples against the test data we have created above.

In the below example, the command is to GROUP data from ORDER’s table by CITY – i.e. we want to get count of orders from different cities.

SELECT city, COUNT(*) AS no_of_orders FROM orders GROUP BY city

cityno_of_orders

Köln1

Scena/Schenna1

Wyoming1

Surendranagar1

Belfast1

Götzis1

Rostock1

Bocchigliero1

San Rosendo1

Essex1

Casole d’Elsa1

Here is another example, where we want to GROUP BY city and find the total revenue generated from each city.

SELECT city, SUM(order_total) AS revenue FROM orders
GROUP BY city

cityrevenue

Köln117.79

Scena/Schenna95.73

Wyoming37.28

Surendranagar95.55

Belfast115.85

Götzis123.7

Rostock88.2

Bocchigliero52.68

San Rosendo124.01

Essex58.99

GROUP BY HAVING

Let’s see an example using the HAVING clause with GROUP BY. We will try to find the revenue for all the cities which have a name starting with ‘C’

SELECT city, COUNT(*) AS no_of_orders FROM orders GROUP BY city HAVING city LIKE 'C%'

cityno_of_orders

Casole d’Elsa1

Cumaribo1

Crystal Springs1

Cherepovets1

Cumberland1

Cardiff1

Cefalà Diana1

Colico1

GROUP BY With ORDERING

We can add ORDERING as well along with the GROUP BY clause. For example – GROUP BY city and revenue numbers of city names beginning with ‘S’ and the rows are listed by decreasing values of revenue.

SELECT city, SUM(order_total) AS revenue FROM orders
GROUP BY city HAVING city LIKE 'S%' ORDER BY revenue DESC

cityrevenue

San Rosendo124.01

Saint-Dié-des-Vosges115.37

St. Paul113.78

Sunshine Coast Regional District110.53

San Giacomo degli Schiavoni96.92

Scena/Schenna95.73

Surendranagar95.55

Singkawang87.34

San Ignacio75.02

Sautin68.99

Swan Hill68.85

Saint-Marc60.72

San Giorgio Albanese45.98

GROUP BY With Multiple Columns

We can use GROUP BY with multiple columns or can be simply called sub-grouping.

For example: Suppose you have order data and you want to GROUP revenue BY – month and year. In this case, we can use multiple columns.

SELECT YEAR(order_date) as _year, MONTH(order_date) as _month, SUM(order_total) as revenue FROM orders
GROUP BY _month, _year HAVING _year='2020' ORDER BY _month DESC

The above query would return a grouped result for the year 2020, grouped by month.

_year_monthrevenue

202012594.84

202011713.17

202010223.68

20209124.01

20208356.91

20207204.13

20206675.59

20205357.05

20204238.36

2020340.79

20202197.73

20201132.31

GROUP BY With ROLLUP

ROLLUP creates an additional aggregate row, depending on the no of columns specified in the GROUP BY query.

Here’s the syntax of using GROUP BY with ROLLUP

SELECT column1, column2, column3
FROM tableName
GROUP BY column1, [column2] with ROLLUP
[HAVING condition]

Now, let’s see some examples with rollup – GROUP BY year and revenue.

SELECT YEAR(order_date) as _year, SUM(order_total) as revenue FROM orders
GROUP BY _year WITH ROLLUP

This ROLLUP command will generate an additional row which would be a summation of all the entries in the GROUP BY – i.e. along with entries of 2019, 2020, and 2021, you will see an entry with a null value for the year and total sum of revenue for all the 3 grouped data.

Output:

_yearrevenue

2019278.64

20203858.57

20213233.69

NULL7370.9

Add another column to group by using the below command:

SELECT YEAR(order_date) as _year, MONTH(order_date) as _month, 
SUM(order_total) as revenue FROM orders GROUP BY _month, _year WITH ROLLUP

Here, you will see one extra ROLLED UP row against each GROUP BY column – i.e. monthly total for all the years (for all months). For example, you will see an extra ROLLED UP row for January 2020 and January 2021 and another final ROLLED UP row for the summation of all the values.

Rolled up columns are highlighted.

_year_monthrevenue

20201132.31

20211508.59

NULL1640.9

20202197.73

20212380.97

NULL2578.7

2020340.79

202010223.68

202110355.8

NULL10579.48

201911157.73

202011713.17

20211199.28

NULL11970.18

201912120.91

202012594.84

NULL12715.75
NULLNULL7370.9

GROUP BY With JOIN

GROUP BY can also be clubbed with MySQL JOINS.

For example: If we want to find the product name and total product quantity in all the orders, we would need to join order_details and product_details table, as the order_details table reference product_id as a FOREIGN KEY from product_details table.

Let’s see how we can use JOIN to fetch that kind of data.

SELECT pd.product_name as prod_name, SUM(od.quantity) as total_quantity FROM order_details od
INNER JOIN product_details pd ON od.product_id=pd.product_id
GROUP BY prod_name

In the above query – you can see that we have done an INNER JOIN on PRODUCT_DETAILS table to fetch the product_name and GROUP BY product_name against total quantity from the ORDER_DETAILS table.

Output:

prod_nametotal_quantity

Biscuits31

Chocolates27

Washing Powder34

Flour12

Batteries34

Floor cleaner32

Toothbrush28

Fruits17

Vegetables32

Sugar15

Frequently Asked Questions

Q #1) Can GROUP BY and ORDER BY used together?

Answer: Yes – GROUP BY can be used along with ORDER BY clause to order or arrange the resultant grouped rows in a specified order – like ascending or descending. For example, suppose we want to GROUP BY revenue in descending order with GROUP BY YEAR from ORDERS table.

SELECT YEAR(order_date) as _year, SUM(order_total) as revenue FROM orders
GROUP BY _year  ORDER BY revenue DESC

_yearrevenue

20203858.57

20213233.69

2019278.64

Q #2) How can you specify conditions in a GROUP BY clause?

Answer: Similar to WHERE clause in normal SELECT queries, in the GROUP BY command we can use the HAVING clause to specify any particular condition which we want to apply to the GROUPED criteria.

For example – Suppose we want to GROUP BY city names in ORDERS table and want to capture cities having name starting with ‘K’.

We can use the below command:

SELECT city, SUM(order_total) AS revenue FROM orders
GROUP BY city HAVING city LIKE 'K%'

CITYREVENUE

Köln117.79

Kech42.63

Khanewal113.46

USING OR with HAVING – Below is the command to include cities with name like – ‘SA’

SELECT city, SUM(order_total) AS revenue FROM orders
GROUP BY city HAVING city LIKE 'K%' or city like 'S%'

CITYREVENUE

Köln117.79

San Rosendo124.01

Saint-Dié-des-Vosges115.37

Saint-Marc60.72

Kech42.63

San Giacomo degli Schiavoni96.92

Sautin68.99

Khanewal113.46

San Ignacio75.02

San Giorgio Albanese45.98

Q #3) How to group by two columns in MySQL?

Answer: Yes, GROUP BY can be used on multiple columns as well. It’s important to note here that when multiple columns are used, the column preference is taken from left to right.
i.e. grouping happens for the leftmost column till it reaches the end of the specified column list.

Syntax for using GROUP BY with multiple columns:

SELECT column1, [AGGREGATE column2]  FROM table GROUP SELECT 
column1, [AGGREGATE column2]  from table GROUP BY column1, column2....... column1, column2.......

Conclusion

In this tutorial, we learned about the MySQL GROUP BY clause. We saw how the GROUP BY clause can be used to generate aggregated groups of data available in one or more tables.

We also looked at different ways we can use GROUP BY to aggregate multiple columns and use options like ROLLUP, GROUPING BY JOINS, etc. GROUP BY is one of the most powerful and widely used commands to run analysis on given data and to drive business decisions out of the conducted analysis.

Bài viết liên quan
Bài Mới Nhất
Alternate Text Gọi ngay