Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums How do I use the GROUP BY clause to aggregate data in SQL Server? – MindStick
Ask Question

1 Answers

Ravi Vishwakarma
Ravi Vishwakarma 12 Jul 2024
Report Abuse

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

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;

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.

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md