CodeCapsule

SQL Aggregate Functions

by

Here’s a clear, complete guide to SQL Aggregate Functions — what they do, how they behave with NULLs, and best-practice examples.

✅ What Are Aggregate Functions?

Aggregate functions take multiple rows and return a single summarized value.

They are often used with GROUP BY.

✅ Common SQL Aggregate Functions

1. COUNT()

Counts rows.

  • COUNT(*) counts all rows

  • COUNT(column) ignores NULLs

SELECT COUNT(*) AS total_users
FROM users;
SELECT COUNT(email) AS users_with_email
FROM users;

2. SUM()

Adds numeric values.

SELECT SUM(amount) AS total_revenue
FROM payments;

3. AVG()

Returns the average of numeric values (ignores NULL).

SELECT AVG(salary) AS avg_salary
FROM employees;

4. MIN() and MAX()

Returns smallest and largest values.

SELECT MIN(price), MAX(price)
FROM products;

🔥 Using Aggregate Functions with GROUP BY

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

🔎 HAVING (Filter After Aggregation)

Use HAVING to filter aggregated results (not WHERE).

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

🧠 Important Behavior Notes

✔ All aggregate functions ignore NULLs, except COUNT(*)
✔ Aggregates without GROUP BY operate on the entire table
✔ Cannot reference non-aggregated columns without GROUP BY

🔧 Combining Aggregates

You can compute multiple aggregates at once:

SELECT
COUNT(*) AS total_orders,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order_value
FROM orders;

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *