WHERE → Filter rows
GROUP BY → Create groups
SUM → Calculate totals
HAVING → Filter groups
This sequence is fundamental to SQL analysis.
1️⃣9️⃣ ORDER BY with GROUP BY
You can sort aggregated results.
Suppose you want regions with the highest sales first:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC;
Result:
North: 500,000, South: 350,000, West: 200,000, East: 150,000
2️⃣0️⃣ Top 3 Regions
You can combine:
GROUP BY + ORDER BY + LIMIT
For example, in PostgreSQL/MySQL:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC
LIMIT 3;
This answers:
"Which three regions generated the most sales?"
2️⃣1️⃣ GROUP BY Dates
Suppose you have:
Order_Date and Sales
You might want:
Total sales by year.
The exact date function varies by database system.
For example, in PostgreSQL:
SELECT
EXTRACT(YEAR FROM Order_Date) AS Sales_Year,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY EXTRACT(YEAR FROM Order_Date)
ORDER BY Sales_Year;
Result:
2024: 8,500,000, 2025: 10,200,000, 2026: 12,400,000
2️⃣2️⃣ Grouping by Month
In PostgreSQL, you can use:
SELECT
DATE_TRUNC('month', Order_Date) AS Sales_Month,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY DATE_TRUNC('month', Order_Date)
ORDER BY Sales_Month;
This creates monthly sales totals.
Different SQL platforms have different date functions, so always check the database you're working with.
2️⃣3️⃣ Calculate Average Order Value
A common business KPI is:
Average Order Value (AOV)
A simple version is:
SELECT
SUM(Sales) / COUNT(*) AS Average_Order_Value
FROM Orders;
If each row represents exactly one order.
If the table can contain multiple rows per order, however, you need to calculate the denominator based on distinct orders:
SELECT
SUM(Sales) / COUNT(DISTINCT Order_ID) AS Average_Order_Value
FROM Orders;
This distinction is extremely important.
2️⃣4️⃣ COUNT(DISTINCT) in Real Analytics
Suppose a customer places multiple orders:
Customer 101 → Orders 5001, 5002
Customer 102 → Order 5003
Customer 103 → Orders 5004, 5005
Total orders: 5
Unique customers: 3
Query:
SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;
Result: 3
This is commonly used for metrics such as:
Active customers
Unique users
Unique accounts
Distinct orders
Distinct products
2️⃣5️⃣ Conditional Aggregation
One powerful technique is combining CASE WHEN with aggregate functions.
For example:
Count how many orders were above ₹50,000.
SELECT
SUM(
CASE
WHEN Sales > 50000 THEN 1
ELSE 0
END
) AS High_Value_Orders
FROM Orders;
This allows you to create customized metrics.
You'll use this technique much more in advanced SQL.
2️⃣6️⃣ Common SQL Analytical Pattern
A very common query structure is:
SELECT
Dimension,
AGGREGATE_FUNCTION(Metric) AS KPI
FROM Table
WHERE Condition
GROUP BY Dimension
HAVING Aggregate_Condition
ORDER BY KPI DESC;
For example:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000
ORDER BY Total_Sales DESC;
