📘 SQL Aggregate Functions

📘 SQL Aggregate Functions – Full Explanation

🔶 What are Aggregate Functions in SQL?

An aggregate function takes multiple rows of data and returns a single value based on that group of rows.

They are used mostly with the GROUP BY clause, but they can also work without it.

📌 These functions ignore NULL values, except COUNT(*), which counts all rows (including NULLs).

✅ List of Common Aggregate Functions

Function	What It Does
`MIN()`	Returns the smallest value
`MAX()`	Returns the largest value
`SUM()`	Returns the total sum of a numeric column
`AVG()`	Returns the average (mean) of values
`COUNT()`	Counts number of rows

🔎 Let's Use This Table for All Examples

🗃️ Input Table: `Sales`

SaleID	Product	Quantity	Price
1	Pen	10	5
2	Pencil	20	2
3	Eraser	15	3
4	Pen	8	5
5	Pencil	25	2

🧠 Theory + Example of Each Function

1️⃣ `MIN()` — Smallest Value

📖 Theory: MIN() returns the lowest value in a column.
Often used to find lowest price, minimum marks, etc.

🧪 Query:

SELECT MIN(Quantity) AS SmallestQty FROM Sales;

📤 Output:

SmallestQty
8

📝 Explanation: Among all quantities, the smallest is 8.

2️⃣ `MAX()` — Largest Value

📖 Theory: MAX() returns the highest value in a column.

🧪 Query:

SELECT MAX(Quantity) AS LargestQty FROM Sales;

📤 Output:

LargestQty
25

📝 Explanation: Among all quantities, the maximum is 25.

3️⃣ `SUM()` — Total Value

📖 Theory: SUM() adds up all values in a numeric column.

🧪 Query:

SELECT SUM(Quantity) AS TotalQty FROM Sales;

📤 Output:

TotalQty
78

📝 Explanation: 10 + 20 + 15 + 8 + 25 = 78

4️⃣ `AVG()` — Average Value

📖 Theory: AVG() finds the average of all values (ignores NULL).

🧪 Query:

SELECT AVG(Quantity) AS AverageQty FROM Sales;

📤 Output:

AverageQty
15.6

📝 Explanation: Total is 78, number of rows is 5 → 78 ÷ 5 = 15.6

5️⃣ `COUNT()` — Count of Rows

📖 Theory: COUNT() counts how many values/rows are present.

COUNT(*): counts all rows
COUNT(column): ignores NULL values

🧪 Query:

SELECT COUNT(*) AS TotalRows FROM Sales;

📤 Output:

TotalRows
5

📝 Explanation: There are 5 rows in total.

🔧 CREATE TABLE Statement

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    Product VARCHAR(50),
    Quantity INT,
    Price INT
);

📝 INSERT INTO Statement

INSERT INTO Sales (SaleID, Product, Quantity, Price) VALUES
(1, 'Pen', 10, 5),
(2, 'Pencil', 20, 2),
(3, 'Eraser', 15, 3),
(4, 'Pen', 8, 5),
(5, 'Pencil', 25, 2);

📘 SQL Aggregate Functions