login

Subscribe to SQL Tutorial websiteRSS Feed
 » SQL Tutorial » SQL GROUP BY

SQL GROUP BY

Summary: In this tutorial, you will learn how to SQL GROUP BY clause to group a set of result set based on group columns.

SQL GROUP BY is used to divide a result set that returns from SELECT statement into groups based on group columns. For each group you can apply aggregate functions such as SUM, AVG, MIN, MAX and COUNT to output the summary information.

SQL GROUP BY is very useful when you want to analyze data in analytical fashion such as how many sale orders was ordered by a customer and sold by a sale person.  It is often used in datawarehouse or BI system to produce the analytical reports.

The common syntax of SQL GROUP BY is as follows:

SELECT c1,c2,... cn, aggregate_function(expression)
FROM tables
WHERE where_conditions
GROUP BY c1, c2, ... cn
ORDER BY order_columns

Let’s take a look at several examples of using SQL GROUP BY to see how it works. We will uses ORDER_DETAILS table in our sample database to practice.

Each sale order is identified by it unique order ID. The items that we sold are stored in a ORDER_DETAILS table. To get sale revenue based on each sale order we can use GROUP By with SUM function as follows:

SELECT orderID, SUM(unitPrice * quantity) AS total
FROM order_details
GROUP BY orderID

Here is the excerpt of the result:

SQL GROUP BY Example 1

In the query above, SQL first looks at the GROUP BY clause and groups the result set into groups based on the order identity. Then SQL computes the total of unitPrice column multiple with quantity column for each group.

You can use SQL GROUP BY without the aggregate functions. In this case, SQL GROUP BY works like SQL DISTINCT statement.

SQL GROUP BY is often used with SQL ORDER BY clause to sort the result set.

For example you can sort the total sale of each sale orders in descending order as the following query:

SELECT OrderID, SUM(unitPrice * quantity) total
FROM Order_details
GROUP BY OrderID
ORDER BY total DESC

Here is the excerpt of the result of the query above:

SQL GROUP BY Example 2

Group by multiple columns with SQL GROUP BY

You can group the result not only by one column but also by multiple columns. 

For example, if you want to know how many sale orders ordered by a customer and sold by a saleman, you can group the result based on both customer and employee.

The the database diagram of related tables is as follows:

SQL GROUP BY - Database Diagram of Related Table

The following illustrates the query:

SELECT b.customerid, 
       b.CompanyName, 
       a.employeeid AS "Sale person ID",
       concat(e.lastname,e.firstname) as "Sale person",
       COUNT(a.orderid) AS "Number of orders"
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

Here is the excerpt returned data from the query above:

SQL GROUP BY Example 3

In this tutorial, you’ve learned how to use SQL GROUP BY to divide records in a result set into groups and apply aggregate function for each group to produce summary output. SQL GROUP BY is often used with SQL HAVING that we will show you in the next tutorial.

Related Tutorials