Overview
TheGROUP BY
clause returns a group of records from a table or multiple tables with the same values as the specified columns.
The result of the GROUP BY
clause returns a single row for each value of the column.
You can use aggregate functions such as
COUNT()
, MAX()
, MIN()
, SUM()
, etc., to perform the operations on the grouped values in the SELECT
statement.Syntax
Ensure the column you are using to group is available in the column list.
a) Basic syntax
The basic syntax of theGROUP BY
clause is as follows −
SELECT column_1, column_2, aggregate_function(column_3)
defines the columns you want to group (column_1, column_2
) and the column that you want to apply an aggregate function to (column_3
).FROM table_name
defines the table where the data comes from.GROUP BY column_1, column_2,...;
lists the columns that you want to group in theGROUP BY
clause.
The column specified in the
SELECT
command must also appear in the GROUP BY
clause.b) Syntax with WHERE
clause
Please take note that the GROUP BY
clause must precisely appear after the WHERE
clause, as shown below:
Examples
Let’s assume that we have two tables here, the customer table and the orders table: customer tableorders table |
---|
#Case 1: Basic GROUP BY
Here we will get all product names by grouping them using the products ordered from the orders table:
#Case 2: GROUP BY
on Multiple Columns
The following example uses multiple columns in the GROUP BY
clause:
#Case 3: GROUP BY
with Aggregate Functions
For this example, we will calculate the total amount each customer has paid for their orders. We will use one of the aggregate functions, i.e., the SUM()
function.
#Case 4: GROUP BY
with JOIN
Condition
Unlike the previous example, the following query joins the orders table with the customer table and groups customers by their names. Here we will use COUNT()
as the aggregate function to count the number of products each customer has purchased.
#Case 5: GROUP BY
with Date Data Type
The order_date
column uses a DATE
data type. In this example, we will group the order’s quantity and total price by dates using the DATE()
function.