Understanding Aggregate Functions

Aggregate functions enable statistical analysis across multiple rows in database tables. Common use cases include calculating total salaries, finding maximum values, or counting records that meet specific criteria.

Common Aggregate Functions

Basic Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE conditions
ORDER BY column_name;

MIN and MAX Functions

These functions work with various data types including dates, numbers, and strings.

SELECT MIN(hire_date), MAX(hire_date) 
FROM employees;

SELECT MIN(salary), MAX(salary) 
FROM employees;

SUM and AVG Functions

These functions operate exclusively on numeric data types.

SELECT SUM(earnings), AVG(earnings) 
FROM employees 
WHERE position LIKE 'SALES%';

COUNT Function Variations

COUNT(*) includes all rows, while COUNT(column_name) excludes NULL values.

SELECT COUNT(*) 
FROM employees 
WHERE department_id = 30;

SELECT COUNT(bonus_amount) 
FROM employees 
WHERE department_id = 30;

Eliminating Duplicates with DISTINCT

SELECT COUNT(DISTINCT department_id) 
FROM employees;

Handilng NULL Values

Most aggregate functions ignore NULL values. Use COALESCE or IFNULL to include them in calculations.

SELECT AVG(bonus_amount), COUNT(bonus_amount) 
FROM employees;

SELECT AVG(COALESCE(bonus_amount, 0)), COUNT(COALESCE(bonus_amount, 0))  
FROM employees;

Grouping Data with GROUP BY

Basic Grouping Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE conditions
GROUP BY column_name
ORDER BY column_name;

SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

SELECT position, COUNT(employee_id) 
FROM employees 
GROUP BY position;

Multi-Column Grouping

SELECT department_id, position, SUM(salary) 
FROM employees 
GROUP BY department_id, position;

Grouping with Multiple Tables

SELECT dept.department_id, department_name, COUNT(employee_id), MAX(salary)  
FROM employees emp, departments dept 
WHERE emp.department_id = dept.department_id 
GROUP BY dept.department_id, department_name;

Filtering Grouped Results with HAVING

HAVING clause filters results after grouping, unlike WHERE which filters before grouping.

SELECT department_id, COUNT(employee_id) 
FROM employees 
GROUP BY department_id 
HAVING COUNT(employee_id) > 3;

SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id 
HAVING MAX(salary) > 2900;

SELECT position, SUM(salary)  
FROM employees 
WHERE position LIKE 'SALES%' 
GROUP BY position 
HAVING SUM(salary) > 5000 
ORDER BY SUM(salary);

SQL Query Execution Order

Complete SELECT statement processsing follows this sequence:

1. FROM clause identifies source tables
2. WHERE clause applies initial filters
3. GROUP BY organizes data into groups
4. HAVING filters grouped results
5. SELECT determines final output columns
6. ORDER BY sorts the result set

SELECT department_id, position, AVG(salary) 
FROM employees 
WHERE position IN ('SALESMAN', 'MANAGER', 'CLERK') 
GROUP BY department_id, position 
HAVING AVG(salary) > 1000 
ORDER BY 3 DESC;