GROUP 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.
COUNT()
, MAX()
, MIN()
, SUM()
, etc., to perform the operations on the grouped values in the SELECT
statement.GROUP 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 the GROUP BY
clause.SELECT
command must also appear in the GROUP BY
clause.WHERE
clauseGROUP BY
clause must precisely appear after the WHERE
clause, as shown below:
orders table |
---|
GROUP BY
GROUP BY
on Multiple ColumnsGROUP BY
clause:
GROUP BY
with Aggregate FunctionsSUM()
function.
GROUP BY
with JOIN
ConditionCOUNT()
as the aggregate function to count the number of products each customer has purchased.
GROUP BY
with Date Data Typeorder_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.