# 📘 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 `NULL`s).

---

### ✅ **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**:

```sql
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**:

```sql
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**:

```sql
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**:

```sql
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**:

```sql
SELECT COUNT(*) AS TotalRows FROM Sales;
```

📤 **Output**:

| TotalRows |
| --- |
| 5 |

📝 *Explanation*: There are **5 rows** in total.

### 🔧 **CREATE TABLE Statement**

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

---

### 📝 **INSERT INTO Statement**

```sql
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);
```