๐๏ธ SQL โ Level 2: Aggregate Functions, GROUP BY & HAVING
Now that you've learned SQL fundamentals, it's time to move from retrieving individual records to summarizing data.
This is one of the most important SQL skills for Data Analysts.
In real interviews and jobs, you'll frequently be asked questions like:
What is the total sales by region?
What is the average salary by department?
How many customers are in each city?
Which products generated more than โน10 lakh in sales?
To answer these questions, you need:
Aggregate Functions + GROUP BY + HAVING
1๏ธโฃ What Are Aggregate Functions?
Aggregate functions perform calculations across multiple rows and return a summarized result.
The most important ones are:
SUM()
COUNT()
AVG()
MIN()
MAX()
Think of them as the SQL equivalent of the basic Excel functions you learned earlier.
2๏ธโฃ SUM()
SUM() calculates the total of a numeric column.
Suppose you have:
Order_ID: 1001, Sales: 50,000
Order_ID: 1002, Sales: 70,000
Order_ID: 1003, Sales: 30,000
Query:
SELECT SUM(Sales) AS Total_Sales
FROM Orders;
Result:
Total_Sales = 150,000
Business question
What is our total revenue?
Answer โ SUM()
3๏ธโฃ COUNT()
COUNT() counts records.
SELECT COUNT(*) AS Total_Orders
FROM Orders;
If there are 10,000 orders:
Total_Orders = 10,000
Why COUNT(*)?
COUNT(*) counts rows.
This is often useful when you want the total number of records.
4๏ธโฃ COUNT(Column)
You can also count values in a specific column.
SELECT COUNT(Customer_ID) AS Customer_Count
FROM Orders;
One important distinction:
COUNT(column) generally doesn't count NULL values.
Whereas:
COUNT(*)
counts rows regardless of whether individual columns contain NULLs.
5๏ธโฃ COUNT(DISTINCT)
Suppose your Orders table contains:
Order 1001 โ Customer 101
Order 1002 โ Customer 102
Order 1003 โ Customer 101
Order 1004 โ Customer 103
There are:
4 orders
but only:
3 unique customers
Use:
SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;
Result:
3
This is extremely important in analytics.
6๏ธโฃ AVG()
AVG() calculates the average.
Suppose salaries are:
50,000, 60,000, 70,000
Query:
SELECT AVG(Salary) AS Average_Salary
FROM Employees;
Result:
60,000
Business questions
What is the average order value?
What is the average employee salary?
What is the average product price?
Answer โ AVG()
7๏ธโฃ MIN()
MIN() returns the smallest value.
SELECT MIN(Salary) AS Minimum_Salary
FROM Employees;
Example result:
35,000
Useful for:
Minimum salary
Lowest sales
Earliest date
Lowest transaction value
8๏ธโฃ MAX()
MAX() returns the largest value.
SELECT MAX(Salary) AS Maximum_Salary
FROM Employees;
Result:
150,000
Useful for:
Highest salary
Highest sales
Largest transaction
Latest date
9๏ธโฃ Using Multiple Aggregate Functions
You can use several aggregate functions in one query.
SELECT
SUM(Sales) AS Total_Sales,
AVG(Sales) AS Average_Sales,
MIN(Sales) AS Minimum_Sales,
MAX(Sales) AS Maximum_Sales,
COUNT(*) AS Total_Orders
FROM Orders;