Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

GROUP BY

The GROUP BY clause is used to group rows that have the same values in one or more columns.

What is GROUP BY?

The GROUP BY clause in SQL Server is used to arrange rows into groups based on one or more columns.

It is mainly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN() and MAX().

In simple words:

GROUP BY takes similar records and puts them into logical groups so that we can perform calculations on each group.

Why Do We Use GROUP BY?

GROUP BY is useful when we want summarized information instead of displaying every individual row.

  • Find the number of employees in each department.
  • Calculate total salary department-wise.
  • Calculate average salary for each department.
  • Find minimum and maximum salary in each department.
  • Generate summary reports from large tables.

GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

Example Table

Consider the following Employees table:

EmployeeId EmployeeName Department Salary
1 Rahul IT 50000
2 Priya HR 40000
3 Amit IT 60000
4 Neha HR 45000
5 Rohit Sales 35000

Basic GROUP BY Example

Suppose we want to find the number of employees in each department.

SELECT Department,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

Output

Department EmployeeCount
HR 2
IT 2
Sales 1

How GROUP BY Works

In the above example, SQL Server looks at the Department column and creates groups for matching values.

IT Group: Rahul, Amit

HR Group: Priya, Neha

Sales Group: Rohit

Then COUNT() calculates the number of records in every group.

GROUP BY with SUM()

We can use GROUP BY with SUM() to calculate the total salary for every department.

SELECT Department,
       SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Result

Department TotalSalary
HR 85000
IT 110000
Sales 35000

GROUP BY with AVG()

AVG() calculates the average salary for every department.

SELECT Department,
       AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

GROUP BY with MIN()

MIN() returns the lowest salary in each department.

SELECT Department,
       MIN(Salary) AS MinimumSalary
FROM Employees
GROUP BY Department;

GROUP BY with MAX()

MAX() returns the highest salary in each department.

SELECT Department,
       MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY Department;

GROUP BY with Multiple Aggregate Functions

We can use multiple aggregate functions in the same query.

SELECT
    Department,
    COUNT(*) AS EmployeeCount,
    SUM(Salary) AS TotalSalary,
    AVG(Salary) AS AverageSalary,
    MIN(Salary) AS MinimumSalary,
    MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY Department;

GROUP BY with HAVING

The HAVING clause is used to filter groups after GROUP BY has been applied.

SELECT Department,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;
Remember:

WHERE filters individual rows, while HAVING filters grouped results.

WHERE vs HAVING

WHERE HAVING
Filters individual rows. Filters groups.
Used before GROUP BY. Used after GROUP BY.
WHERE Salary > 40000 HAVING COUNT(*) > 1

Important GROUP BY Rule

When a column is selected without an aggregate function, that column should normally be included in the GROUP BY clause.

SELECT Department,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

Real-World Uses of GROUP BY

  • Department-wise employee reports.
  • City-wise customer reports.
  • Product-category sales reports.
  • Monthly sales summaries.
  • Branch-wise transaction reports.
  • Category-wise product analysis.

Important Points

  • GROUP BY groups rows with the same values.
  • It is commonly used with aggregate functions.
  • COUNT(), SUM(), AVG(), MIN() and MAX() are common aggregate functions.
  • HAVING is used to filter groups.
  • GROUP BY is very useful for reporting and data analysis.

Summary

GROUP BY is used to divide rows into groups based on common values. It is mainly used with aggregate functions to generate summarized information. GROUP BY is an important SQL Server concept used extensively in reports, dashboards and data analysis.