Summary: in this tutorial, you will learn how to the SQL GROUP BY clause to group rows into a set of summary rows by the values of columns or expressions.
Introduction to SQL GROUP BY clause
GROUP BY clause is used to group rows returned by SELECT statement into a set of summary rows or groups based on values of columns or expressions. You can apply an aggregate function such as SUM, AVG, MIN, MAX or COUNT to each group to output the summary information.
GROUP BY clause is very useful when you want to analyze data in analytical fashion e.g., products were purchased by a customer or sold by a sale person by quarter. Therefore you often find the
GROUP BY clause applied in the data warehouse and business intelligence (BI) systems to produce the analytical reports.
The typical syntax of
GROUP BY is as follows:
SELECT column1, column2, aggregate_function(expression) FROM table_name WHERE condition GROUP BY column1;
SQL GROUP BY examples
Let’s take a look at the
SQL GROUP BY with SUM function example
To get the total units in stock for each product category, you use the
GROUP BY clause with the SUM function as follows:
SELECT categoryid, SUM(unitsinstock) FROM products GROUP BY categoryid;
The database engine performs the following steps:
- First, examines the
GROUP BYclause and divides the products into groups based on the product category
- Second, calculates the total of units in stock by using the
SUMfunction for each group.
SQL GROUP BY with COUNT function example
The following query selects the number of products in each product category by using the
GROUP BY clause with the
SELECT categoryid, COUNT(productid) FROM products GROUP BY categoryid;
SQL GROUP BY with AVG function
You can check the average number of units in stock for each product category by using the
GROUP BY clause and
AVG function as the following query:
SELECT categoryid, FLOOR(AVG(unitsinstock)) FROM products GROUP BY categoryid;
FLOOR function is used to get the largest integer value that is not greater than the argument.
SQL GROUP BY with MIN and MAX functions
Apply the same technique, you can select minimum and maximum units in stock for each product category as follows:
SELECT categoryid, MIN(unitsinstock), MAX(unitsinstock) FROM products GROUP BY categoryid;
SQL GROUP BY with ORDER BY example
GROUP BY clause is used in conjunction with the ORDER BY clause to sort the groups. For example, you can sort product categories by the number of products as the following query:
SELECT categoryid, COUNT(productid) FROM products GROUP BY categoryid ORDER BY COUNT(productid) DESC;
SQL GROUP BY multiple columns
You can group the result set by not only one column but also multiple columns. For example, if you want to know how many sale orders that were ordered by a customer and sold by a sale person, you can group the result set based on both customer and sale person.
The database diagram of related tables is as follows:
The following query illustrates the idea:
Code language: PHP (php)
SELECT b.customerid, b.CompanyName, COUNT(a.orderid) AS 'Orders', CONCAT(e.lastname, e.firstname) as 'Sale Person' FROM orders a INNER JOIN customers b ON a.customerid = b.customerid INNER JOIN employees e ON e.employeeid = a.employeeid GROUP BY b.customerid, a.employeeid ORDER BY b.customerid ASC, 'Number of orders' DESC;
In this tutorial, you have learned how to use the SQL
GROUP BY clause to divide rows into groups and apply the aggregate function to each group to produce summary output.