GROUP BY SQL
Introduction
In the previous tutorials, we have seen the LIKE operator. We can filter the data in certain columns using the LIKE operator. In this tutorial, we shall look into the GROUP BY SQL to group the data.
Understanding Data Grouping – GROUP BY SQL
Before looking into the GROUP BY in SQL command, we shall look into the example to understand the data grouping.
Example
SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = ‘DLL01’;
Output
num_prods
———–
4
In the above example, we have to count the products with the specific vendor id. But if we want to find the number of products by each vendor, then we have to group the data accordingly. Grouping plays a role to divide the data into different groups and help us to perform the operations on the grouped data accordingly.
Creating Groups
It can be used in a clause along with the SELECT command. Shall look into the example for the better understanding of grouping the data.
Example
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
Output
vend_id num_prods
——— ———
BRS01 3
DLL01 4
FNG01 2
The above example states that, from the Products table, the vendor id is selected as the specific column and it is used to count the num_prods column in the table. Here, the clause is used to group the data by the vendor id to calculate the product count based on the vendor id as the grouping factor rather than the entire table.
From the above example to this example, you can sort out the difference that in the first case total products are counted and shown. But here in the second case, the product count is shown by grouping them into the specific groups by vendor id.
- Group clause can contain as many columns as you want.
- Many of the SQL databases do not allow the grouping for the columns with the variable type data types.
- Group clause usage must be done after the SELECT command and before the ORDER BY command.
Filtering Groups
As of the previous tutorial, filtering the data using the LIKE command is discussed. Now, using the GROUP BY, we can ensure the SQL to include or exclude certain fields. For example, if you want to know the list of the customers, who have made at least two orders in the entire data, then you need to filter the data and group the data accordingly. So, to know about the filtering the data let us look into the following example
Example
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
Output
cust_id orders
———- ———–
1000000001 2
Now, let us look into another example to distinguish between the WHERE clause and HAVING clause filtering. WHERE clause filters the data before grouping whereas, HAVING clause filters the data after the grouping is done.
Example
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;Output
vend_id num_prods
———- ———–
BRS01 3
FNG01 2
Grouping and Sorting
It is very important to know the difference between the GROUP BY SQL and ORDER BY values, even though both of them will show a similar output. Let us look into the two different examples to clearly interpret the difference between them.
Example
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;Output
order_num items
———- —–
20006 3
20007 5
20008 5
20009 3
This shows the example of GROUP BY SQL.
Example
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;Output
order_num items
———- —–
20006 3
20009 3
20007 5
20008 5
From the above examples, we can understand that this will only group the data whereas, ORDER BY clause will both group the data and sort the data accordingly.
This is all about the end of this tutorial, about the GROUP BY SQL. For any sort of queries or doubts, you can reach out through the comment box.
Related Tutorials