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_namedefines 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.
🧐 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:
Syntax
|
SELECT
column_1, column_2, aggregate_function(column_3)FROM
table_name
WHERE
conditions
GROUPBY
column_1, column_2,...;
Examples
Let’s assume that we have two tables here, the customer table and the orders table:
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.
The query above will return the output as shown below:
Output
|
+-----------+----------+| cust_id | sum |+-----------+----------+|11009|10000||11007|10000||11006|28000||11002|10000||11001|14000||11008|20000|+-----------+----------+
#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.
Group by query
|
SELECT C.cust_name,COUNT(order_prod)FROM orders O
JOIN customer C ON O.cust_id = C.cust_id
GROUPBY C.cust_name;
The above command will create the following result:
Output
|
+------------+---------+| cust_name | count |+------------+---------+| Tom |1|| Chris |1|| Casey |2|| Maya |2|| Sean |1|| Emily |1|+------------+---------+
#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.
The above query will generate the following result:
Output
|
+---------------+------------+---------+|date| order_qty | sum |+---------------+------------+---------+|2021-07-27|2|8000||2021-08-29|4|20000||2021-04-17|1|10000||2021-09-30|1|10000||2021-05-04|2|20000||2021-01-08|1|4000||2021-12-18|2|10000||2021-10-30|2|10000|+---------------+------------+---------+