How do I use the GROUP BY clause to aggregate data in SQL Server?

Question

Ravi Vishwakarma · Answer

The GROUP BY clause in 
SQL Server is used to group rows that have the same values in specified columns into aggregate data, such as sums, averages, counts, etc. Here is a step-by-step guide on how to use the
GROUP BY clause:
Basic Syntax
SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY column1, column2;
Aggregate Functions
Common aggregate functions include:
SUM()COUNT()AVG()MIN()MAX()
Example Use Cases
1. Sum of Sales by Salesperson
Assume we have a table Sales with the following columns: SaleID,
SaleAmount, SalesPersonID, and SaleDate.
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleAmount DECIMAL(10, 2),
    SalesPersonID INT,
    SaleDate DATE
);
INSERT INTO Sales (SaleID, SaleAmount, SalesPersonID, SaleDate)
VALUES
(1, 100.00, 1, '2024-01-01'),
(2, 200.00, 2, '2024-01-02'),
(3, 300.00, 1, '2024-01-03'),
(4, 150.00, 2, '2024-01-04'),
(5, 250.00, 3, '2024-01-05');
To calculate the total sales amount for each salesperson:
SELECT SalesPersonID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID;
Output:
SalesPersonID
TotalSales
1
400.00
2
350.00
3
250.00
2. Count of Sales by Salesperson
To count the number of sales made by each salesperson:
SELECT SalesPersonID, COUNT(*) AS NumberOfSales
FROM Sales
GROUP BY SalesPersonID;
Output:
SalesPersonID
NumberOfSales
1
2
2
2
3
1
3. Average Sale Amount by Salesperson
To calculate the average sale amount for each salesperson:
SELECT SalesPersonID, AVG(SaleAmount) AS AverageSaleAmount
FROM Sales
GROUP BY SalesPersonID;
Output:
SalesPersonID
AverageSaleAmount
1
200.00
2
175.00
3
250.00
Using GROUP BY with Multiple Columns
You can group by multiple columns to get more granular aggregates.
SELECT SalesPersonID, SaleDate, SUM(SaleAmount) AS DailySales
FROM Sales
GROUP BY SalesPersonID, SaleDate;
Output:
SalesPersonID
SaleDate
DailySales
1
2024-01-01
100.00
1
2024-01-03
300.00
2
2024-01-02
200.00
2
2024-01-04
150.00
3
2024-01-05
250.00
Filtering Groups with HAVING
The HAVING clause is used to filter groups based on aggregate functions.
Example: Total Sales Greater than 300
SELECT SalesPersonID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
HAVING SUM(SaleAmount) > 300;
Output:
SalesPersonID
TotalSales
1
400.00
2
350.00
and 
Complete Example
Combining everything together, here is a full example including table creation, data insertion, and querying with
GROUP BY and HAVING:
-- Create table
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleAmount DECIMAL(10, 2),
    SalesPersonID INT,
    SaleDate DATE
);
-- Insert sample data
INSERT INTO Sales (SaleID, SaleAmount, SalesPersonID, SaleDate)
VALUES
(1, 100.00, 1, '2024-01-01'),
(2, 200.00, 2, '2024-01-02'),
(3, 300.00, 1, '2024-01-03'),
(4, 150.00, 2, '2024-01-04'),
(5, 250.00, 3, '2024-01-05');
-- Query with GROUP BY and HAVING
SELECT SalesPersonID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
HAVING SUM(SaleAmount) > 300;
and 
Read more
Write a query to retrieve the total number of employees in each department.
How do I use CTE to simplify complex queries in SQL Server?
Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL Server.
and

forum

How do I use the GROUP BY clause to aggregate data in SQL Server?

ICSM Computer

Can you answer this question?

1 Answers

Aggregate Functions

Example Use Cases

1. Sum of Sales by Salesperson

2. Count of Sales by Salesperson

3. Average Sale Amount by Salesperson

Using GROUP BY with Multiple Columns

Filtering Groups with HAVING

Complete Example

Liked By

SalesPersonID	SaleDate	DailySales
1	2024-01-01	100.00
1	2024-01-03	300.00
2	2024-01-02	200.00
2	2024-01-04	150.00
3	2024-01-05	250.00