---
title: "How do I use the GROUP BY clause to aggregate data in SQL Server?"  
description: "How do I use the GROUP BY clause to aggregate data in SQL Server?"  
author: "ICSM Computer"  
published: 2024-07-11  
updated: 2024-07-12  
canonical: https://www.mindstick.com/forum/160897/how-do-i-use-the-group-by-clause-to-aggregate-data-in-sql-server  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012", "sql server 2022"]  
reading_time: 4 minutes  

---

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

How do I use the [GROUP BY clause](https://www.mindstick.com/forum/262/group-by-clause) to [aggregate data](https://www.mindstick.com/forum/160910/help-with-writing-a-subquery-to-get-aggregate-data-in-sql-server) in SQL Server?

## Replies

### Reply by Ravi Vishwakarma

The `GROUP BY` [clause](https://www.mindstick.com/forum/33937/difference-between-throw-exception-and-throw-clause) in [SQL Server](https://www.mindstick.com/blog/303288/how-executing-sql-queries-in-sql-server-management-studio-ssms-and-displaying-results) is used to [group](https://yourviews.mindstick.com/view/81323/adani-green-energy-group-bags-world-s-biggest-solar-bid) rows that have the same values in specified columns into [aggregate](https://www.mindstick.com/blog/52/aggregate-functions-in-database) [data](https://www.mindstick.com/articles/13050/salesforce-aiming-to-dominate-predictive-analytics-with-data-science), such as sums, averages, counts, etc. Here is a step-by-step guide on how to use the `GROUP BY` clause:

## Basic Syntax

```plaintext
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`.

```plaintext
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:

```plaintext
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:

```plaintext
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:

```plaintext
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.

```plaintext
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

```plaintext
SELECT SalesPersonID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
HAVING SUM(SaleAmount) > 300;
```

## Output:

| SalesPersonID | TotalSales |
| --- | --- |
| 1 | 400.00 |
| 2 | 350.00 |

#### Complete Example

Combining everything together, here is a full example including table creation, data insertion, and querying with `GROUP BY` and `HAVING`:

```plaintext
-- 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;
```

## 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?**](https://www.mindstick.com/blog/304466/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.**](https://www.mindstick.com/blog/304465/difference-between-inner-join-left-join-right-join-and-full-outer-join-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160897/how-do-i-use-the-group-by-clause-to-aggregate-data-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.