Tutorials›SQL›Aggregate Functions
🟢 Free Demo
SQL TutorialTopic 7 of 20

Aggregate Functions

COUNT, SUM, AVG, MIN, MAX — summarise your data

✅ What You Will Learn

›How COUNT() counts rows or non-NULL values
›How SUM() calculates the total of a numeric column
›How AVG() calculates the average
›How MIN() and MAX() find the smallest and largest values
›How to combine multiple aggregate functions in one query
›How DISTINCT works inside aggregate functions

Aggregate functions perform calculations across multiple rows and return a single value. They are the foundation of all summary reports — total sales, average order value, count of customers, highest and lowest prices.

Without aggregate functions, you can only see individual rows. With them, you can answer "how many?", "how much total?", "what is the average?", "what is the highest/lowest?" — the questions that actually drive business decisions.

📋 The orders table used in examples

order_idcustomer_nameproductamountcity
1001Rahul SharmaLaptop45000Delhi
1002Priya VermaMobile Phone18000Noida
1003Amit KumarHeadphones3500Gurgaon
1004Sneha KapoorLaptop52000Delhi
1005Vikram SinghTablet28000Noida

Syntax

SQL SYNTAX
COUNT(column)   -- count rows
SUM(column)     -- add up all values
AVG(column)     -- calculate the average
MIN(column)     -- find the smallest value
MAX(column)     -- find the largest value

Examples

Example 1COUNT — how many rows
-- Count total number of orders
SELECT COUNT(*) AS total_orders
FROM orders;
OUTPUT
total_orders
------------
4
💡

COUNT(*) counts all rows including those with NULL values. COUNT(column_name) skips NULLs.

Example 2SUM — total revenue
SELECT SUM(amount) AS total_revenue
FROM orders;
OUTPUT
total_revenue
-------------
118500
Example 3AVG — average order value
SELECT AVG(amount) AS avg_order_value
FROM orders;
OUTPUT
avg_order_value
---------------
29625.00
Example 4MIN and MAX together
SELECT MIN(amount) AS smallest_order,
       MAX(amount) AS largest_order,
       MAX(amount) - MIN(amount) AS range_amount
FROM orders;
OUTPUT
smallest_order | largest_order | range_amount
---------------|---------------|-------------
3500           | 52000         | 48500
Example 5Multiple aggregates with a filter
SELECT COUNT(*) AS laptop_orders,
       SUM(amount) AS laptop_revenue,
       AVG(amount) AS avg_laptop_price
FROM orders
WHERE product = 'Laptop';
OUTPUT
laptop_orders | laptop_revenue | avg_laptop_price
--------------|----------------|----------------
2             | 97000          | 48500.00

📌 Key Points to Remember

  • ✓Aggregate functions collapse many rows into one summary value
  • ✓COUNT(*) counts all rows; COUNT(col) skips NULLs
  • ✓SUM and AVG only work on numeric columns
  • ✓MIN and MAX work on numbers, text (alphabetical), and dates
  • ✓You can combine multiple aggregates in one SELECT

🏢 Real-World Application

Aggregate functions are the backbone of every business report. Monthly revenue reports use SUM(amount). Customer lifetime value uses SUM(amount) per customer. Average order value — a key e-commerce metric — uses AVG(order_total). Headcount reporting uses COUNT(employee_id). Inventory management uses MIN(stock_qty) to find items running low. Data analysts at Meesho, Nykaa, Urban Company, and every other analytics-driven company write aggregate queries dozens of times per day to answer questions like "What was our highest sale today?" (MAX) and "How many new users signed up this week?" (COUNT).

⚠️ Common Mistakes to Avoid

WRONGUsing COUNT(*) vs COUNT(column_name) incorrectly
FIXCOUNT(*) counts all rows including NULLs. COUNT(column_name) counts only non-NULL values in that column. Use COUNT(*) for total row count, COUNT(col) when NULLs should be excluded.
WRONGMixing aggregate and non-aggregate columns without GROUP BY
FIXSELECT product, SUM(amount) FROM orders is invalid without GROUP BY product. Either aggregate all columns or use GROUP BY to specify how to group the non-aggregated columns.
WRONGUsing SUM on a text column
FIXSUM only works on numeric columns. Applying SUM to a text column causes an error. Always verify the data type before applying numeric aggregates.
✏️Test Yourself

Which function returns the total sum of a numeric column?

❓ Frequently Asked Questions

What are aggregate functions in SQL?

Aggregate functions perform a calculation on a set of rows and return a single value. The five main aggregate functions are COUNT (count rows), SUM (total of values), AVG (average), MIN (smallest value), and MAX (largest value).

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows in the result, including rows with NULL values. COUNT(column_name) counts only rows where that specific column is not NULL. Use COUNT(*) for total rows, COUNT(col) to count non-empty entries.

Can I use multiple aggregate functions in one SELECT?

Yes. SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders is a valid query that returns five aggregated values in one result row.

What does AVG() do with NULL values?

AVG() ignores NULL values — it calculates the average of only the non-NULL rows. This is important to understand because it means AVG may not reflect the true mean if NULLs represent zero rather than missing data.

How do I count distinct values in SQL?

Use COUNT(DISTINCT column_name). For example, SELECT COUNT(DISTINCT customer_name) FROM orders counts how many unique customers placed orders, not how many orders were placed.

✏️ Practice Exercise

Write a query to find the total revenue, number of orders, and highest single order amount for orders placed after 2026-01-15.

← PreviousLIMIT and TOPNext →GROUP BY
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.