Learn COUNT, SUM, AVG, MIN, MAX for Beginners - Textnotes

Learn COUNT, SUM, AVG, MIN, MAX for Beginners

Master SQL aggregate functions and grouping in this beginner-friendly tutorial. Learn to use COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING clauses to summarize and analyze data. Practice counting employees per department, calculating average salaries, and finding departments with maximum or minimum salaries.

1. Introduction

Aggregate functions in SQL allow you to perform calculations on multiple rows of data and return a single summarized value. Combined with grouping, they are essential for reporting and analytics.

Key Points:

  1. Aggregate functions summarize data.
  2. GROUP BY groups rows with the same values into summary rows.
  3. HAVING filters grouped data, unlike WHERE which filters individual rows.

2. Aggregate Functions

FunctionDescriptionExample
COUNT()Counts rowsCOUNT(*) counts all rows in a table
SUM()Adds valuesSUM(Salary)
AVG()Calculates averageAVG(Salary)
MIN()Finds minimumMIN(Salary)
MAX()Finds maximumMAX(Salary)

Examples:


-- Count total employees
SELECT COUNT(*) AS TotalEmployees
FROM Employees;

-- Sum of all salaries
SELECT SUM(Salary) AS TotalSalary
FROM Employees;

-- Average salary
SELECT AVG(Salary) AS AverageSalary
FROM Employees;

-- Minimum and maximum salary
SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary
FROM Employees;

3. GROUP BY Clause

GROUP BY groups rows with the same values in one or more columns.

Syntax:


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

Examples:


-- Count employees per department
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

-- Average salary per department
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

4. HAVING Clause

HAVING filters the grouped results, similar to WHERE but for groups.

Syntax:


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

Examples:


-- Departments with average salary above 50000
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;

-- Departments with maximum salary above 70000
SELECT Department, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
HAVING MAX(Salary) > 70000;

5. Practical Exercises

  1. Count employees per department.
  2. Calculate the average salary per department.
  3. Find departments with maximum and minimum salaries.
  4. List departments with more than 5 employees using HAVING.
  5. Find departments where the total salary exceeds 300000.

6. Tips for Beginners

  1. Use COUNT() to quickly know the number of rows.
  2. Use GROUP BY with aggregate functions for summary reports.
  3. Use HAVING to filter groups after aggregation.
  4. Avoid using WHERE with aggregate functions; HAVING is used instead.


Next Step: After mastering aggregate functions and grouping, the next module is Joins and Relationships, where you’ll learn INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and self-joins for combining data from multiple tables.