|
Getting your Trinity Audio player ready...
|
When working with data, we rarely want individual rows.
We usually want answers like:
- What is the total sales?
- What is the average product price?
- What is the highest incentive given?
- How many customers do we have?
These calculations are done using Aggregate Functions.
What Are Aggregate Functions?
Aggregate functions perform calculations on multiple rows and return a single value.
Instead of showing 1000 sales records, they summarize data into one meaningful result.
FMCG Dataset Tables Used
We will use:
- employees
- customers
- products
- sales
- incentive_data
Also Read: INSERT Data in SQL
COUNT() – Count Records
Used to count number of rows.
Count Total Customers
SELECT COUNT(*)
FROM customers;✔ Returns total number of customers.
Count Non-Null Values
SELECT COUNT(discount)
FROM sales;This counts only rows where discount is NOT NULL.
SUM() – Add Values
Used to calculate total.
Total Sales Amount
SELECT SUM(sale_amount)
FROM sales;Business Meaning:
Total revenue generated.
AVG() – Average Value
Used to calculate mean value.
Average Product Price
SELECT AVG(price)
FROM products;Business Meaning:
Average product cost.
Average Sales Value
SELECT AVG(sale_amount)
FROM sales;MIN() – Smallest Value
Lowest Product Price
SELECT MIN(price)
FROM products;MAX() – Highest Value
Highest Sale Amount
SELECT MAX(sale_amount)
FROM sales;Business Meaning:
Largest transaction recorded.
ROUND() – Control Decimal Output
Very useful for reporting.
PostgreSQL
SELECT ROUND(AVG(sale_amount), 2)
FROM sales;MySQL
SELECT ROUND(AVG(sale_amount), 2)
FROM sales;Same syntax in both databases.
COALESCE() – Handle NULL Values
If SUM() returns NULL (no data), reports may break.
PostgreSQL & MySQL
SELECT COALESCE(SUM(sale_amount), 0)
FROM sales;If no rows exist → returns 0 instead of NULL.
Extremely useful for MIS dashboards.
Using Aggregate with WHERE
Aggregate functions often use filtering.
Total Sales in 2024
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2024-01-01' AND <= '2024-12-31';Count Active Employees
SELECT COUNT(*)
FROM employees
WHERE status = 'Active';Multiple Aggregates in One Query
SELECT
COUNT(*) AS total_sales,
SUM(sale_amount) AS total_revenue,
AVG(sale_amount) AS average_sale,
MAX(sale_amount) AS highest_sale,
MIN(sale_amount) AS lowest_sale
FROM sales;This gives a quick business summary.
Important Rule About Aggregates
If you use aggregate functions with normal columns:
❌ Wrong:
SELECT city, SUM(sale_amount)
FROM sales;This will give error.
You must use GROUP BY.
Read More: GROUP BY in SQL
PostgreSQL vs MySQL Differences
| Feature | PostgreSQL | MySQL |
|---|---|---|
| COUNT, SUM, AVG | ✅ Yes | ✅ Yes |
| ROUND() | ✅ Yes | ✅ Yes |
| COALESCE() | ✅ Yes | ✅ Yes |
| Strict GROUP BY | Strict | Depends on SQL mode |
PostgreSQL is stricter (which is good for learning clean SQL).
Real Business Examples (FMCG)
Total Revenue Generated
SELECT SUM(sale_amount) AS total_revenue
FROM sales;Average Incentive Given
SELECT AVG(incentive_amount)
FROM incentive_data;Highest Paying Customer Order
SELECT MAX(sale_amount)
FROM sales;Number of Products in Inventory
SELECT COUNT(*)
FROM inventory;Common Mistakes Beginners Make
❌ Forgetting WHERE condition
❌ Ignoring NULL values
❌ Mixing aggregate + non-aggregate columns
❌ Not using ROUND for reports
Why Aggregate Functions Matter for Data Analysts
Aggregate functions help you:
- Build dashboards
- Create KPI reports
- Calculate performance metrics
- Analyze trends
- Generate management summaries
Without aggregates, SQL is just data retrieval.
With aggregates, SQL becomes analytics.
Summary
Aggregate functions in SQL such as COUNT(), SUM(), AVG(), MIN(), and MAX() are used to summarize large datasets into meaningful results. They help calculate totals, averages, highest and lowest values, and record counts. Functions like ROUND() and COALESCE() improve report readability and handle NULL values effectively.
Both PostgreSQL and MySQL support these functions with similar syntax. Aggregate functions are essential for reporting, dashboard creation, and real-world business analysis in FMCG and other industries.