The GROUP BY statement in SQL Server is used to group rows that have the same values in specified columns into summary rows. It is often used in combination with aggregate functions such as COUNT, SUM, AVG, MAX, and MIN to perform calculations on each group of rows.

Syntax

Here's the basic syntax of the GROUP BY statement:

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

Example

Consider a table called Sales with the following structure:

CREATE TABLE Sales (
    SaleID INT,
    SaleYear INT,
    Product VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleID, SaleYear, Product, SalesAmount)
VALUES
(1, 2023, 'ProductA', 1000),
(2, 2023, 'ProductB', 1500),
(3, 2024, 'ProductA', 2000),
(4, 2024, 'ProductB', 2500);

To find the total sales amount for each year, you can use the GROUP BY statement as follows:

SELECT SaleYear, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SaleYear;

Result

The result of the above query will look like this:

SaleYear | TotalSales
---------|------------
2023     | 2500
2024     | 4500

Using Multiple Columns in GROUP BY

You can group by multiple columns to get more detailed summaries. For example, to find the total sales amount for each product in each year:

SELECT SaleYear, Product, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SaleYear, Product;

Result

The result will look like this:

SaleYear | Product  | TotalSales
---------|----------|------------
2023     | ProductA | 1000
2023     | ProductB | 1500
2024     | ProductA | 2000
2024     | ProductB | 2500

HAVING Clause

The HAVING clause is used to filter groups based on a condition. It is similar to the WHERE clause but is used with aggregate functions.

SELECT Product, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Product
HAVING SUM(SalesAmount) > 1500;

Result

The result will show only products with total sales greater than 1500:

Product  | TotalSales
---------|------------
ProductA | 3000
ProductB | 4000

The GROUP BY statement is powerful for generating summarized reports and analyzing data across different dimensions. If you have specific scenarios or need further examples, feel free to let me know!