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 SERVER.
First we Create a sample table name as ITEM_DETAILS and Insert some reocrds 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 purchased 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 an individual Item from table Item_Details.
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 an individual item from table Item_Details.
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.
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 – year and sort 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 individual 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
Recommended for you
1,263 total views, 1 views today
How get Last date purchased Qty???
Ex. Hard Disk – 270, Pen drive 360 , scanner 250