The group by clause in SQL allows you to aggregate records into a set of groups as specified in the columns.
Let us discover how we can use the group by via multiple columns.
Syntax
We can express the syntax as shown below:
FROM TABLE_NAME
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
Let us illustrate how we can use the group by clause.
Example 1
Consider the table shown below:
We can group the records by a single column, as shown in the query below:
FROM products
GROUP BY product_name;
The above query tells SQL to group all the rows with the same product_name into a single group. We then use the count function to determine how many rows are in that group.
The code above should return output as shown:
Example 2
But what about grouping via multiple columns? Using the same table, let us group the data using the product_name and quantity columns.
An example query is provided below:
QUANTITY,
COUNT(*)
FROM PRODUCTS
GROUP BY PRODUCT_NAME,
QUANTITY;
In this case, SQL will group the data if the values of both the product_name and quantity are the same.
The code above should return the query as shown:
Note: Since the first orange has a quantity of 120 and the second with a quantity of 100, SQL does not classify them in the same group.
Closing
In this article, we discovered how to use the group by clause and group by via multiple columns.
