COUNT, SUM, AVG, MIN, MAX

COUNT Function

The COUNT() function returns the number of rows that match a specified condition. It's one of the most commonly used aggregate functions.

COUNT Variations

-- COUNT(*) counts all rows (including NULLs)
SELECT COUNT(*) AS total_rows FROM employees;

-- COUNT(column) counts non-NULL values
SELECT COUNT(phone) AS employees_with_phone FROM employees;

-- COUNT(DISTINCT column) counts unique non-NULL values
SELECT COUNT(DISTINCT department) AS unique_departments FROM employees;

Sample Data

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    phone VARCHAR(20)
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date, phone)
VALUES
    ('John', 'Smith', 'Engineering', 85000, '2020-01-15', '555-0101'),
    ('Jane', 'Doe', 'Marketing', 72000, '2019-06-20', '555-0102'),
    ('Bob', 'Johnson', 'Engineering', 92000, '2018-03-10', NULL),
    ('Alice', 'Williams', 'HR', 68000, '2021-09-01', '555-0104'),
    ('Charlie', 'Brown', 'Marketing', 71000, '2020-11-15', NULL),
    ('Diana', 'Lee', NULL, 95000, '2017-05-22', '555-0106');

COUNT Examples

-- Total number of employees
SELECT COUNT(*) AS total_employees FROM employees;
-- Result: 6

-- Employees with phone numbers
SELECT COUNT(phone) AS with_phone FROM employees;
-- Result: 4 (NULLs excluded)

-- Employees with assigned departments
SELECT COUNT(department) AS with_department FROM employees;
-- Result: 5 (one NULL excluded)

-- Unique departments
SELECT COUNT(DISTINCT department) AS unique_depts FROM employees;
-- Result: 4 (Engineering, Marketing, HR, NULL)

-- Count with condition
SELECT COUNT(*) AS high_earners FROM employees WHERE salary > 80000;
-- Result: 3

COUNT vs COUNT(*)

-- COUNT(*) counts all rows regardless of NULLs
SELECT COUNT(*) FROM employees;
-- Result: 6

-- COUNT(column) excludes NULLs
SELECT COUNT(department) FROM employees;
-- Result: 5

-- COUNT(DISTINCT column) counts unique non-NULLs
SELECT COUNT(DISTINCT department) FROM employees;
-- Result: 4

COUNT Performance

-- COUNT(*) is optimized in most databases
SELECT COUNT(*) FROM large_table;

-- COUNT(column) may be slower if many NULLs
SELECT COUNT(optional_column) FROM large_table;

-- COUNT(DISTINCT column) can be expensive
SELECT COUNT(DISTINCT column) FROM large_table;

COUNT Best Practices

1. Use COUNT(*) to count all rows
2. Use COUNT(column) to count non-NULL values
3. Use COUNT(DISTINCT column) for unique values
4. Be aware of NULL behavior

-- Good: Clear intent
SELECT COUNT(*) AS total FROM employees;
SELECT COUNT(phone) AS with_phone FROM employees;

-- Avoid: Ambiguous
SELECT COUNT(1) FROM employees;  -- Same as COUNT(*) but less clear

COUNT is essential for understanding the size and composition of your data.

SUM and AVG Functions

SUM() calculates the total of a numeric column. AVG() calculates the average. Both ignore NULL values.

SUM Function

-- Total salary of all employees
SELECT SUM(salary) AS total_payroll FROM employees;

-- Total salary by department
SELECT 
    department,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

-- Sum with condition
SELECT SUM(salary) AS high_earner_total
FROM employees
WHERE salary > 80000;

AVG Function

-- Average salary
SELECT AVG(salary) AS avg_salary FROM employees;

-- Average salary by department
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Average with condition
SELECT AVG(salary) AS avg_engineering_salary
FROM employees
WHERE department = 'Engineering';

NULL Behavior

-- NULLs are ignored in calculations
-- If salary has NULLs, they don't affect the result

-- Example: SUM ignores NULLs
SELECT SUM(salary) AS total FROM employees;
-- Only sums non-NULL salary values

-- Example: AVG ignores NULLs
SELECT AVG(salary) AS average FROM employees;
-- Divides by count of non-NULL salaries, not total rows

-- To include NULLs as 0, use COALESCE
SELECT AVG(COALESCE(salary, 0)) AS avg_including_nulls FROM employees;

Rounding Results

-- Round average to 2 decimal places
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

-- Round to whole number
SELECT ROUND(AVG(salary), 0) AS avg_salary FROM employees;

-- Floor and ceiling
SELECT 
    FLOOR(AVG(salary)) AS floor_avg,
    CEIL(AVG(salary)) AS ceil_avg
FROM employees;

Multiple Aggregates

-- Multiple aggregates in one query
SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_payroll,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

-- By department
SELECT 
    department,
    COUNT(*) AS headcount,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

SUM and AVG with Expressions

-- Sum of computed values
SELECT SUM(salary * 0.1) AS total_bonuses FROM employees;

-- Average of computed values
SELECT AVG(salary / 12) AS avg_monthly_salary FROM employees;

-- Conditional sum
SELECT 
    SUM(CASE WHEN department = 'Engineering' THEN salary ELSE 0 END) AS engineering_total,
    SUM(CASE WHEN department = 'Marketing' THEN salary ELSE 0 END) AS marketing_total
FROM employees;

SUM and AVG Best Practices

1. Use COALESCE when NULLs should count as 0
2. Round results for display
3. Use conditional aggregation for pivoted reports
4. Be aware of data types - integer division truncates

-- Integer division issue
SELECT AVG(10, 20, 30);  -- Returns 20 (correct)

-- Integer division with integers
SELECT SUM(quantity) / COUNT(*) FROM orders;  -- Truncates

-- Better: Use decimal types
SELECT SUM(CAST(quantity AS DECIMAL)) / COUNT(*) FROM orders;

SUM and AVG are fundamental for financial and statistical calculations.

MIN and MAX Functions

MIN() returns the minimum value in a column. MAX() returns the maximum value. They work with numeric, string, and date columns.

Basic Usage

-- Minimum and maximum salary
SELECT 
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

-- By department
SELECT 
    department,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- Earliest and latest hire dates
SELECT 
    MIN(hire_date) AS earliest_hire,
    MAX(hire_date) AS latest_hire
FROM employees;

MIN/MAX with Strings

-- Alphabetical first and last names
SELECT 
    MIN(first_name) AS first_alphabetically,
    MAX(first_name) AS last_alphabetically
FROM employees;

-- Shortest and longest names
SELECT 
    MIN(LENGTH(first_name)) AS shortest_name,
    MAX(LENGTH(first_name)) AS longest_name
FROM employees;

MIN/MAX with Dates

-- Date range
SELECT 
    MIN(hire_date) AS first_hire,
    MAX(hire_date) AS last_hire,
    DATEDIFF(MAX(hire_date), MIN(hire_date)) AS days_span
FROM employees;

-- Most recent order per customer
SELECT 
    customer_id,
    MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;

Finding Records with MIN/MAX

-- Find employee with highest salary (wrong way)
SELECT * FROM employees WHERE salary = MAX(salary);  -- ERROR!

-- Correct way: Use subquery
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- Or use ORDER BY with LIMIT
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 1;

-- Find employees with above-average salary
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

MIN/MAX with NULLs

-- NULLs are ignored by MIN and MAX
SELECT 
    MIN(phone) AS earliest_phone,
    MAX(phone) AS latest_phone
FROM employees;
-- Only considers non-NULL phone values

-- Include NULLs as specific values
SELECT 
    MIN(COALESCE(phone, '000-0000')) AS min_phone,
    MAX(COALESCE(phone, '999-9999')) AS max_phone
FROM employees;

MIN/MAX Performance

-- MIN/MAX on indexed columns are very fast
SELECT MIN(salary) FROM employees;  -- Uses index if available
SELECT MAX(hire_date) FROM employees;  -- Uses index if available

-- MIN/MAX with functions may not use indexes
SELECT MIN(UPPER(first_name)) FROM employees;  -- May not use index

MIN/MAX Best Practices

1. Use for finding boundaries - first/last, min/max values
2. Combine with subqueries to find full rows
3. Be aware of NULL behavior - NULLs are ignored
4. Consider indexes for performance

-- Good: Find record with max value
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- Also good: Using JOIN
SELECT e.* FROM employees e
INNER JOIN (
    SELECT MAX(salary) AS max_salary FROM employees
) m ON e.salary = m.max_salary;

MIN and MAX are essential for finding extremes and boundaries in your data.

NULL Behavior in Aggregate Functions

Understanding how aggregate functions handle NULL values is crucial for accurate calculations.

NULL Behavior Summary

Function	NULL Behavior
COUNT(*)	Counts all rows (NULLs included)
COUNT(column)	Excludes NULLs
COUNT(DISTINCT column)	Excludes NULLs
SUM(column)	Excludes NULLs
AVG(column)	Excludes NULLs from both numerator and denominator
MIN(column)	Excludes NULLs
MAX(column)	Excludes NULLs

Examples

-- Sample data with NULLs
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    amount DECIMAL(10,2),
    commission DECIMAL(10,2)
);

INSERT INTO sales VALUES
    (1, 101, 500.00, 50.00),
    (2, 102, NULL, NULL),
    (3, 101, 300.00, 30.00),
    (4, 103, NULL, 20.00),
    (5, 102, 700.00, NULL);

COUNT Behavior

-- COUNT(*) includes all rows
SELECT COUNT(*) FROM sales;
-- Result: 5

-- COUNT(amount) excludes NULLs
SELECT COUNT(amount) FROM sales;
-- Result: 3

-- COUNT(commission) excludes NULLs
SELECT COUNT(commission) FROM sales;
-- Result: 3

-- COUNT(DISTINCT product_id) excludes NULLs
SELECT COUNT(DISTINCT product_id) FROM sales;
-- Result: 3

SUM and AVG Behavior

-- SUM ignores NULLs
SELECT SUM(amount) FROM sales;
-- Result: 1500.00 (500 + 300 + 700)

-- SUM with COALESCE to treat NULLs as 0
SELECT SUM(COALESCE(amount, 0)) FROM sales;
-- Result: 1500.00 (same in this case)

-- AVG ignores NULLs (both numerator and denominator)
SELECT AVG(amount) FROM sales;
-- Result: 500.00 (1500 / 3, not 1500 / 5)

-- AVG treating NULLs as 0
SELECT AVG(COALESCE(amount, 0)) FROM sales;
-- Result: 300.00 (1500 / 5)

MIN/MAX Behavior

-- MIN ignores NULLs
SELECT MIN(amount) FROM sales;
-- Result: 300.00

-- MAX ignores NULLs
SELECT MAX(amount) FROM sales;
-- Result: 700.00

-- MIN with all NULLs returns NULL
SELECT MIN(commission) FROM sales WHERE amount IS NULL;
-- Result: NULL (only NULL commission values exist)

Conditional Aggregation with NULLs

-- Count NULLs explicitly
SELECT 
    COUNT(*) AS total_rows,
    COUNT(amount) AS non_null_amounts,
    COUNT(*) - COUNT(amount) AS null_amounts
FROM sales;

-- Sum including NULLs as 0
SELECT 
    SUM(amount) AS sum_excluding_nulls,
    SUM(COALESCE(amount, 0)) AS sum_including_nulls
FROM sales;

-- Conditional count
SELECT 
    COUNT(CASE WHEN amount IS NULL THEN 1 END) AS null_amounts,
    COUNT(CASE WHEN amount IS NOT NULL THEN 1 END) AS non_null_amounts
FROM sales;

NULL Handling Best Practices

1. Use COALESCE when NULLs should be treated as 0 or default
2. Use COUNT(*) to count all rows including NULLs
3. Use COUNT(column) to count only non-NULL values
4. Document NULL behavior in your queries
5. Consider DEFAULT constraints to prevent NULLs

-- Good: Explicit NULL handling
SELECT 
    COALESCE(SUM(amount), 0) AS total_amount,
    COALESCE(AVG(amount), 0) AS avg_amount
FROM sales;

-- Good: Design tables to minimize NULLs
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    commission DECIMAL(10,2) NOT NULL DEFAULT 0
);

Understanding NULL behavior prevents common calculation errors and ensures accurate results.