SQL Grouped Analysis Examples for Data Analysts

From raw data to business insights
Suppose your Orders table contains one million transactions.
Reading every row individually tells you very little.
Grouped analysis transforms those transactions into information.
Raw Transactions
↓
GROUP BY
↓
Aggregate Function
↓
Business Summary
↓
Decision
This is how dashboards are created.
The role of GROUP BY
GROUP BY organizes rows into categories.
Aggregate functions summarize each category.
Think of them as partners.
GROUP BY
Creates groups
↓
COUNT
SUM
AVG
MIN
MAX
Calculate summaries
Neither is complete without the other.
Business Example 1 — Count Customers by City
Business Question
"How many customers do we have in each city?"
SQL
SELECT customer_city,
COUNT(*) AS total_customers
FROM Customers
GROUP BY customer_city;
Business Interpretation
Instead of one customer count,
the business now understands customer distribution across every city.
This helps with:
regional expansion,
sales planning,
warehouse placement,
localized marketing.
Business Example 2 — Revenue by Category
Business Question
"Which product categories generate the most revenue?"
SQL
SELECT category_id,
SUM(order_total) AS revenue
FROM Orders
GROUP BY category_id;
Business Interpretation
Management immediately sees which categories contribute most to sales.
This information influences:
promotions,
inventory,
pricing,
purchasing decisions.
Business Example 3 — Average Salary by Department
Business Question
"What is the average salary in each department?"
SQL
SELECT department_id,
AVG(employee_salary) AS average_salary
FROM Employees
GROUP BY department_id;
Business Interpretation
HR can compare departments fairly instead of looking at individual salaries.
Business Example 4 — Earliest Order by Customer
Business Question
"When did every customer place their first order?"
SQL
SELECT customer_id,
MIN(order_date) AS first_order
FROM Orders
GROUP BY customer_id;
Business Interpretation
This query is frequently used for:
customer lifecycle analysis,
retention analysis,
first purchase tracking.
Business Example 5 — Highest Order by Customer
Business Question
"What is the largest purchase each customer has ever made?"
SQL
SELECT customer_id,
MAX(order_total) AS highest_order
FROM Orders
GROUP BY customer_id;
Business Interpretation
This helps identify:
premium customers,
enterprise buyers,
upselling opportunities.
Business Example 6 — Orders by Month
Business Question
"How many orders were received each month?"
SQL
SELECT order_month,
COUNT(*) AS total_orders
FROM Orders
GROUP BY order_month;
Business Interpretation
This helps identify:
seasonal demand,
business growth,
holiday spikes,
declining periods.
Business Example 7 — Average Order Value by City
Business Question
"Which cities have the highest spending customers?"
SQL
SELECT customer_city,
AVG(order_total) AS average_order
FROM Orders
GROUP BY customer_city;
Business Interpretation
Marketing can focus premium campaigns on cities with higher purchasing behavior.
Business Example 8 — Lowest Inventory by Warehouse
Business Question
"Which warehouses are closest to stock shortages?"
SQL
SELECT warehouse_id,
MIN(stock_quantity) AS lowest_stock
FROM Inventory
GROUP BY warehouse_id;
Business Interpretation
Operations teams can replenish inventory before stockouts occur.
Business Example 9 — Highest Revenue Month
Business Question
"Which month generated the highest revenue?"
SQL
SELECT order_month,
SUM(order_total) AS monthly_revenue
FROM Orders
GROUP BY order_month;
Business Interpretation
Finance can identify seasonal peaks and forecast future sales more accurately.
Business Example 10 — Campaign Performance
Business Question
"Which marketing campaigns generated the most customers?"
SQL
SELECT campaign_name,
COUNT(DISTINCT customer_id) AS customers
FROM Campaigns
GROUP BY campaign_name;
Business Interpretation
Marketing teams can invest more budget in campaigns that attract the most unique customers.
Combining GROUP BY with HAVING
Most dashboards don't display every group.
They display only meaningful groups.
Example
SELECT customer_city,
SUM(order_total) AS revenue
FROM Orders
GROUP BY customer_city
HAVING SUM(order_total) > 500000;
Business Question
"Show only cities generating more than ₹5,00,000 in revenue."
The result is cleaner,
more focused,
and far more useful for executives.
Combining GROUP BY with ORDER BY
Business reports are usually sorted.
Example
SELECT category_id,
SUM(order_total) AS revenue
FROM Orders
GROUP BY category_id
ORDER BY revenue DESC;
Business Question
"Rank product categories by revenue."
Ranking is often more valuable than simply displaying numbers.
Combining GROUP BY with WHERE
Example
SELECT customer_city,
COUNT(*) AS customers
FROM Customers
WHERE customer_status='Active'
GROUP BY customer_city;
Business Question
"How many active customers are there in each city?"
Notice the order.
WHERE
↓
GROUP BY
↓
Aggregate Function
↓
HAVING
Understanding this execution order prevents many beginner mistakes.
Visual Framework
Raw Data
↓
WHERE
↓
GROUP BY
↓
Aggregate Function
↓
HAVING
↓
ORDER BY
↓
Business Report
This is the workflow behind a large percentage of SQL dashboards.
Which Aggregate Function Should You Use?
Need quantity?
↓
COUNT
Need total value?
↓
SUM
Need average?
↓
AVG
Need lowest value?
↓
MIN
Need highest value?
↓
MAX
This decision tree can solve most aggregation problems.
Common Business Reporting Patterns
Sales Dashboard
Revenue by month
Revenue by region
Orders by city
Marketing Dashboard
Customers by campaign
Average acquisition cost
Conversion count
Finance Dashboard
Monthly revenue
Largest payment
Average invoice amount
Operations Dashboard
Lowest inventory
Average delivery time
Orders by warehouse
HR Dashboard
Employees by department
Average salary
Highest salary
Notice that every department relies on grouped analysis.
Common Mistakes
1. Forgetting GROUP BY
Trying to mix aggregated values with non-aggregated columns causes SQL errors.
2. Using WHERE instead of HAVING
Remember
WHERE
Filters rows.
HAVING
Filters groups.
3. Choosing the wrong aggregate function
Always begin with the business question.
Don't begin with SQL syntax.
4. Forgetting NULL behavior
All aggregate functions except COUNT(*) ignore NULL values.
Understanding this prevents incorrect reporting.
5. Looking only at one metric
Revenue alone rarely tells the full story.
Combine metrics.
For example:
Revenue
Orders
Average Order Value
Together,
they provide a much richer understanding.
Real Analyst Workflow
Imagine the CEO asks:
"Prepare a dashboard showing customer growth, revenue, average order value, and highest-selling regions."
Your thought process becomes:
Break the dashboard into individual metrics.
Choose the correct aggregate function for each metric.
Decide the grouping level.
Apply filters.
Sort the output.
Build the report.
That workflow is exactly how most production dashboards are developed.
Analyst Tip
When creating grouped reports, never stop after writing the SQL query.
Always look at the results and ask:
"If I were the manager reading this report, what decision would I make?"
If the report doesn't support a business decision, it probably needs another metric, a different grouping, or better filtering.
The Grito Factor
Most Business Intelligence dashboards don't contain any advanced SQL. They rely on a surprisingly small toolkit: GROUP BY, aggregate functions, filtering, and sorting. Master these fundamentals well, and you'll be able to build a large percentage of real-world analytical reports before learning advanced SQL features.
Interview Perspective
Interviewers often use grouped analysis questions because they test multiple SQL concepts at once.
Common questions include:
Calculate revenue by month.
Count customers by city.
Find departments with the highest average salary.
Show products with more than 100 sales.
Explain the difference between WHERE and HAVING.
Interviewers are usually testing your ability to solve business problems, not just your ability to remember syntax.
Practice Questions
Beginner
Count the number of customers in each country.
Calculate total revenue for every product category.
Intermediate
Find the average salary in every department.
Show the highest order value for each city.
Advanced
Create a report showing only those product categories where:
Total revenue exceeds ₹10,00,000.
Average order value exceeds ₹2,500.
Products are ranked from highest revenue to lowest.
(Hint: Combine SUM, AVG, GROUP BY, HAVING, and ORDER BY.)
Final Thoughts
Individual aggregate functions are useful on their own.
But real business analysis begins when you combine them with GROUP BY, WHERE, HAVING, and ORDER BY.
That combination transforms raw transactional data into dashboards, KPI reports, executive summaries, and business insights.
By mastering grouped analysis, you've crossed an important milestone in your SQL journey—from retrieving data to analyzing it.
Continue Your Learning
You've now completed the SQL Aggregation cluster.
Related lessons:
SQL Aggregate Functions
COUNT
SUM
AVG
MIN
MAX
GROUP BY
HAVING
ORDER BY
Your next recommended module is the SQL Joins cluster, where you'll learn how to combine data from multiple tables before applying the aggregation techniques you've mastered in this cluster to solve more complex business problems.