Aggregation
## Learning Objectives
- Master aggregate functions
- Use GROUP BY effectively
- Filter groups with HAVING
- Understand COUNT, SUM, AVG, MIN, MAX
## Aggregate Functions
### Common Functions
| Function | Description |
|----------|-------------|
| COUNT() | Count rows |
| SUM() | Sum values |
| AVG() | Average values |
| MIN() | Minimum value |
| MAX() | Maximum value |
## COUNT
### Count All Rows
```sql
SELECT COUNT(*)
FROM employees;
```
### Count Non-NULL Values
```sql
SELECT COUNT(email)
FROM employees;
```
### Count Distinct
```sql
SELECT COUNT(DISTINCT department)
FROM employees;
```
## SUM
```sql
SELECT SUM(salary)
FROM employees;
```
### With WHERE
```sql
SELECT SUM(salary)
FROM employees
WHERE department = 'Sales';
```
### Multiple Columns
```sql
SELECT
SUM(salary) AS total_salary,
SUM(bonus) AS total_bonus
FROM employees;
```
## AVG
```sql
SELECT AVG(salary)
FROM employees;
```
### AVG with WHERE
```sql
SELECT AVG(salary)
FROM employees
WHERE department = 'Engineering';
```
### ROUND Results
```sql
SELECT ROUND(AVG(salary), 2)
FROM employees;
```
## MIN and MAX
```sql
SELECT
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees;
```
### Date Examples
```sql
-- Earliest hire
SELECT MIN(hire_date)
FROM employees;
-- Latest hire
SELECT MAX(hire_date)
FROM employees;
```
## GROUP BY
### Basic Grouping
```sql
SELECT department, COUNT(*) as count
FROM employees
GROUP BY department;
```
### GROUP BY Result
```text
department | count
-------------+------
Engineering | 15
Sales | 12
Marketing | 8
```
### GROUP BY Multiple Columns
```sql
SELECT
department,
city,
COUNT(*) as headcount,
AVG(salary) as avg_salary
FROM employees
GROUP BY department, city;
```
### Aggregate Without GROUP BY
```sql
-- All aggregate, no grouping
SELECT
COUNT(*) as total_employees,
SUM(salary) as total_payroll
FROM employees;
```
## HAVING
### Filter Groups
```sql
SELECT
department,
COUNT(*) as headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
```
### WHERE vs HAVING
| Clause | Filters | Applied |
|--------|---------|---------|
| WHERE | Rows | Before GROUP BY |
| HAVING | Groups | After GROUP BY |
### Combined Example
```sql
SELECT
department,
COUNT(*) as headcount,
AVG(salary) as avg_salary
FROM employees
WHERE salary > 40000
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC;
```
### Execution Order
1. WHERE filters rows
2. GROUP BY groups filtered rows
3. HAVING filters groups
4. ORDER BY sorts result
## COUNT with NULL
```sql
-- COUNT(*) counts all rows including NULL
SELECT COUNT(*) FROM employees; -- All rows
-- COUNT(column) counts non-NULL values only
SELECT COUNT(manager_id) FROM employees; -- Only non-NULL
```
## AVG and NULL
```sql
-- AVG ignores NULL values
-- AVG(1, 2, NULL, 4) = AVG(1, 2, 4) = 2.33
```
## GROUP BY with JOIN
```sql
SELECT
d.department_name,
COUNT(e.employee_id) as headcount,
AVG(e.salary) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name
ORDER BY avg_salary DESC;
```
## COUNT with Case
```sql
SELECT
department,
COUNT(*) AS total,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive
FROM employees
GROUP BY department;
```
## DISTINCT in Aggregates
```sql
-- Count unique departments
SELECT COUNT(DISTINCT department)
FROM employees;
-- Average distinct salary (if duplicates exist)
SELECT AVG(DISTINCT salary)
FROM employees;
```
## Multiple Aggregates
```sql
SELECT
department,
COUNT(*) as headcount,
SUM(salary) as total_salary,
ROUND(AVG(salary), 2) as avg_salary,
MIN(salary) as min_salary,
MAX(salary) as max_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
```
## Summary
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX
- GROUP BY groups rows for aggregation
- HAVING filters grouped results (WHERE filters rows)
- COUNT(*) counts all rows; COUNT(column) counts non-NULL
- Aggregate functions ignore NULL (except COUNT(*))
- Use ORDER BY after GROUP BY for sorted results
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →