SQL GROUP BY

SQL GROUP BY Statement  is used to arrange identical data into groups with the help of Aggregate functions.

SYNTAX

SELECT COLUMN_1, COLUMN_2, .., AGGREGATE_FUNCTION(COLUMN_NAME) 
FROM TABLE_NAME [WHERE CONDITION] 
GROUP BY COLUMN_1, COLUMN_2, .., 
ORDER BY ASC|DESC

The COLUMN_1, COLUMN_2, .., are not summarized within an aggregate function and must be included in the GROUP BY clause.

AGGREGATE_FUNCTION can be any SQL valid function such as COUNT, SUM, MIN, MAX, or AVG .

[WHERE CONDITION] is a WHERE CLAUSE that is used to specify any condition. It is an optional.

ORDER BY is used to short result either in ascending or descending order, by default ascending order. It is an optional.

Lets look at an example of Group By in SQL.

First we create a sample table name as ITEM_DETAILS  and insert some records into table as given below.

CREATE TABLE dbo.ITEM_DETAILS (
ID INT NOT NULL, 
ITEM VARCHAR(50), 
ITEM_QTY INT, 
ITEM_PURCHASED_DATE DATETIME)

INSERT INTO dbo.ITEM_DETAILS (ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE)
VALUES (1, 'Scanner', 300, '2019-12-01 00:00:00.000'),
(2, 'Scanner', 250, '2019-12-10 00:00:00.000'),
(3, 'Scanner', 110, '2019-11-12 00:00:00.000'),
(4, 'Scanner', 110, '2019-09-19 00:00:00.000'),
(5, 'Scanner', 280, '2019-09-25 00:00:00.000'),
(6, 'Scanner', 180, '2019-08-15 00:00:00.000'),
(7, 'Scanner', 180, '2019-07-08 00:00:00.000'),
(8, 'Pen Drive', 360, '2019-12-15 00:00:00.000'),
(9, 'Pen Drive', 270, '2019-09-11 00:00:00.000'),
(10, 'Pen Drive', 210 ,'2019-08-22 00:00:00.000'),
(11, 'Pen Drive', 190 ,'2019-08-19 00:00:00.000'),
(12, 'Pen Drive', 480 ,'2019-07-15 00:00:00.000'),
(13, 'Pen Drive', 680, '2019-06-25 00:00:00.000'),
(14, 'Pen Drive', 120, '2019-06-18 00:00:00.000'),
(15, 'Hard Disk', 270, '2019-09-19 00:00:00.000'),
(16, 'Hard Disk', 210, '2019-08-12 00:00:00.000'),
(17, 'Hard Disk', 190, '2019-08-29 00:00:00.000'),
(18, 'Hard Disk', 480, '2019-07-25 00:00:00.000'),
(19, 'Hard Disk', 680, '2019-06-15 00:00:00.000'),
(20, 'Hard Disk', 120, '2019-06-28 00:00:00.000')

Using GROUP BY with Aggregate functions

GROUP BY With SUM() function

To display the total sum of quantity by Item.

SELECT ITEM , SUM(ITEM_QTY) AS  TOTAL_QTY 
FROM dbo.ITEM_DETAILS 
GROUP BY Item

GROUP BY WITH MAX() function

To display the last purchased date of each item.

SELECT ITEM, MAX(ITEM_PURCHASED_DATE) AS LAST_PURCHASED_DATE
FROM dbo.ITEM_DETAILS
GROUP BY ITEM

Using GROUP BY WITH MIN() function

To display the first purchased date of each item.

SELECT ITEM, MIN(ITEM_PURCHASED_DATE) AS FIRST_PURCHASED_DATE
FROM dbo.ITEM_DETAILS
GROUP BY ITEM

Using GROUP BY WITH COUNT() function

To display the total number of records for each Item in table.

SELECT ITEM, COUNT(ID) AS Cnt
FROM dbo.ITEM_DETAILS
GROUP BY ITEM

Using GROUP BY WITH AVG() function

To display the total average quantity purchased for each item.

SELECT ITEM, AVG(ITEM_QTY) AS AVG_QTY
FROM dbo.ITEM_DETAILS
GROUP BY ITEM

Using GROUP BY With WHERE Clause

To display total sum of quantity for item Scanner and Pen Drive.

SELECT ITEM, SUM(ITEM_QTY) AS TTL_QTY
FROM ITEM_DETAILS
WHERE ITEM IN ('Scanner', 'Pen Drive')
GROUP BY ITEM

As you can see, it returns total sum of quantity for Scanner and Pen Driver.

Using Group By With Order By Clause

Display the total purchased quantity by month and year and sort the result by month and year in descending order.

SELECT MONTH(ITEM_PURCHASED_DATE) AS MONTH, 
YEAR(ITEM_PURCHASED_DATE) AS YEAR, 
SUM(ITEM_QTY) AS TTL_QTY
FROM dbo.ITEM_DETAILS
GROUP BY MONTH(ITEM_PURCHASED_DATE), YEAR(ITEM_PURCHASED_DATE) 
ORDER BY YEAR(ITEM_PURCHASED_DATE) DESC, 
MONTH(ITEM_PURCHASED_DATE) DESC

As you can see, the total number of quantity purchased in each month and year, also result are sorted by month in descending order.

Perform multiple aggregation with GROUP By clause

To get the total quantity, counts, average quantity, first and last purchased date for each item.

SELECT ITEM, 
MIN(ITEM_PURCHASED_DATE) AS FIRST_PURCHASED_DATE,
MAX(ITEM_PURCHASED_DATE) AS LAST_PURCHASED_DATE, 
SUM(ITEM_QTY) AS TOTAL_QTY,
COUNT(ID) AS COUNT, 
AVG(ITEM_QTY) AS AVG_QTY
FROM dbo.ITEM_DETAILS
GROUP BY ITEM

Also Read..

HAVING CLAUSE