Concept of SQL GROUP BY Statement
The GROUP BY statement is passed down to group the rows that have the same values in the table.
Grouping allows you to divide data into logical sets so that you can perform a collection of calculations on each group. Groups are built for using the GROUP BY clause in the SELECT statement.
The GROUP BY clause commands the DB2 to group the data and then operate the collection on each group rather than on the entire result set.
The GROUP BY statement is often used with combine functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the conclusion-set by one or more columns.
Syntax
SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
ORDER BY column_name;
Table name Customers
| Customer Id | Name | Address | City | Postal code | Country |
|---|---|---|---|---|---|
|
001 |
Ankit | New Ashok Nagar H.NO 67/6 | Lucknow | 82878 | India |
|
002 |
Vinay | B clash society L810 | Pune | 67567 | India |
|
003 |
Raju | Mathur Road near ICIC bank H.no7/290 | Delhi | 29867 | India |
|
004
|
Karan | H.no 456 near kv school | Noida | 98689 | India |
|
005 |
Sumit | Near SPGI hospital house67 | Goa | 76687 | India |
Example
SELECT COUNT(CustomerId), City
FROM Customers
GROUP BY City;
SELECT COUNT(CustomerID), City
FROM Customers
GROUP BY City
ORDER BY COUNT(CustomerId) ASC;
GROUP BY With JOIN Example
Table Name Order
| Order Id | Customer Id | Employee Id | Order Date | Shipped Id |
|---|---|---|---|---|
| 1029 | 008 | 628790 | 2020-09-04 | 11900 |
| 1022 | 009 | 272989 | 2020-07-04 | 12900 |
| 1078 | 005 | 282896 | 2020-03-04 | 13900 |
| 1078 | 004 | 278626 | 2020-11-04 | 14900 |
Table Name Shippers
| Employee Id | Shipped Id | Companies Name |
|---|---|---|
| 008 | 11 | DTDC Courier company |
| 006 | 77 | Blue Dart Courier Service |
SELECT Shippers.CompaniesName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperId = Shippers.ShipperId
GROUP BY CompaniesName;