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;

Keywords: