SUM, AVG, MIN and MAX with GROUP BY for Sales Analytics

Beyond simply counting records, businesses constantly need numeric summaries: total revenue per region, average order value per customer segment, the highest and lowest prices within a product category. SUM, AVG, MIN, and MAX are the four aggregate functions that answer exactly these kinds of numeric questions, and combined with GROUP BY, they form the analytical backbone of virtually every sales and financial report you will ever build in SQL.

Key Definitions

• SUM: An aggregate function that adds together all the values in a numeric column within each group.
• AVG: An aggregate function that calculates the arithmetic mean of a numeric column's values within each group.
• MIN: An aggregate function that returns the smallest value in a column within each group.
• MAX: An aggregate function that returns the largest value in a column within each group.

What You'll Learn

• Apply SUM with GROUP BY to calculate total values per category, such as total revenue per region.
• Apply AVG with GROUP BY to calculate average values per category, such as average order value per customer.
• Apply MIN and MAX with GROUP BY to find the smallest and largest values within each group.
• Combine multiple aggregate functions in a single GROUP BY query for a comprehensive summary.
• Round and format aggregate results appropriately for business-friendly reporting.

Detailed Explanation

SUM, AVG, MIN, and MAX all follow the same core pattern established in the previous lessons: GROUP BY first organizes rows into buckets, and then each aggregate function computes its specific summary value independently within each of those buckets.

SUM adds up every value in the specified column within a group, making it ideal for total-based questions like 'total revenue per region' or 'total quantity sold per product.' AVG computes the arithmetic mean within each group, answering questions like 'average order value per customer' or 'average salary per department.' It is important to remember that AVG, like SUM, ignores NULL values in its calculation rather than treating them as zero — a NULL value is excluded from both the sum and the count used to calculate the average, which can meaningfully affect the result compared to if those NULLs were mistakenly treated as zero.

MIN and MAX find the smallest and largest values respectively within each group, useful for questions like 'what is the cheapest and most expensive product in each category' or 'what is the earliest and latest order date per customer.' Unlike SUM and AVG, which only make sense for numeric data, MIN and MAX can also be meaningfully applied to dates and even text columns, where they return the earliest/latest date or the alphabetically first/last value respectively.

In real reporting, these four functions are frequently combined in a single query to produce a comprehensive summary table in one pass, such as showing total revenue, average order value, and the minimum and maximum order value side by side for each region — exactly the kind of report a sales manager would want to review at a glance.

Visual Summary

Draw a small table of five orders for 'North' region with amounts 1200, 3400, 800, 2100, 1500. Below it, draw four labeled output boxes computed from these same five values: 'SUM = 9000', 'AVG = 1800', 'MIN = 800', 'MAX = 3400', each with a small arrow pointing back to the relevant value(s) in the original table (MIN pointing to 800, MAX pointing to 3400).

Quick Reference

region	total_revenue (SUM)	avg_order_value (AVG)	min_order (MIN)	max_order (MAX)
North	9000	1800	800	3400
South	5200	1300	600	2200
West	12400	2066.67	1100	4300

SQL Example

CREATE TABLE orders (
  order_id      INT PRIMARY KEY AUTO_INCREMENT,
  region        VARCHAR(50),
  order_amount  DECIMAL(10,2),
  order_date    DATE
);

INSERT INTO orders (region, order_amount, order_date) VALUES
  ('North', 1200, '2026-01-05'),
  ('North', 3400, '2026-01-12'),
  ('North', 800,  '2026-02-01'),
  ('North', 2100, '2026-02-14'),
  ('North', 1500, '2026-03-03');

-- Comprehensive sales summary per region
SELECT
  region,
  SUM(order_amount)              AS total_revenue,
  ROUND(AVG(order_amount), 2)    AS avg_order_value,
  MIN(order_amount)              AS smallest_order,
  MAX(order_amount)              AS largest_order,
  MIN(order_date)                AS first_order_date,
  MAX(order_date)                AS last_order_date
FROM orders
GROUP BY region;

This query groups all orders by region and computes five summary metrics in a single pass: SUM for total revenue, AVG (rounded to two decimal places) for average order value, MIN and MAX on order_amount for the smallest and largest individual orders, and MIN and MAX on order_date to show the first and most recent order dates within each region — a complete sales analytics snapshot per region in one query.

Real-World Examples

• Retail analytics teams use SUM grouped by store_location to report total daily or monthly revenue across all branches.
• Subscription businesses use AVG grouped by pricing_tier to monitor average revenue per user within each subscription plan.
• E-commerce platforms use MIN and MAX grouped by product_category to display price ranges (from cheapest to most expensive) to shoppers browsing a category.
• HR departments use AVG grouped by department to compare average salary levels across different teams for pay equity analysis.
• Logistics companies use MIN and MAX grouped by route to identify the fastest and slowest delivery times recorded on each route.

Common Mistakes to Avoid

• Assuming AVG treats NULL as zero, leading to a misunderstanding of why the computed average differs from manual expectations.
• Using SUM alone to compare performance across groups of very different sizes, without considering that AVG might give a fairer comparison.
• Forgetting that MIN and MAX can be applied to date and text columns, not just numeric ones.
• Not rounding AVG results for business-facing reports, producing unnecessarily long decimal values in the output.

Interview Questions

Q1. How would you calculate total revenue per region using SQL?

Group the orders table by region and apply SUM on the order_amount column, producing one total revenue figure for each distinct region.

Q2. Does AVG treat NULL values as zero when calculating an average?

No. AVG excludes NULL values entirely from both the total sum and the count of rows used in its calculation, rather than treating them as zero, which would otherwise incorrectly lower the computed average.

Q3. Can MIN and MAX be used on non-numeric columns like dates or text?

Yes. MIN and MAX work on dates (returning the earliest and latest date) and even text columns (returning the alphabetically first and last value), not just numeric columns.

Q4. How would you find the highest and lowest order amount within each region in a single query?

Group the orders table by region, and include both MAX(order_amount) and MIN(order_amount) in the SELECT list, which computes both extremes for each region in one pass.

Q5. Why might a business prefer AVG over SUM when comparing performance across regions of very different sizes?

SUM can be misleading when comparing regions with very different numbers of orders, since a region with more orders will naturally have a higher total even if its typical order is smaller. AVG normalizes for this by showing the typical order value regardless of order count, enabling a fairer comparison.

Practice MCQs

1. Which aggregate function would you use to find total revenue per region?

1. AVG
2. SUM
3. MIN
4. COUNT

Answer: B. SUM

Explanation: SUM adds together all order amounts within each region group, producing the total revenue figure.

2. How does AVG handle NULL values in its calculation?

1. Treats them as zero
2. Excludes them from both the sum and the count
3. Causes an error
4. Treats them as the maximum value

Answer: B. Excludes them from both the sum and the count

Explanation: AVG ignores NULL values entirely rather than treating them as zero, which would otherwise skew the average incorrectly lower.

3. MIN and MAX can be meaningfully applied to:

1. Only integer columns
2. Numeric, date, and text columns
3. Only columns with no NULL values
4. Only columns used in GROUP BY

Answer: B. Numeric, date, and text columns

Explanation: MIN and MAX work across numeric, date (earliest/latest), and even text (alphabetically first/last) columns, not just numbers.

4. A report comparing 'typical order value' across regions with very different order volumes should generally use:

1. SUM
2. COUNT
3. AVG
4. MAX only

Answer: C. AVG

Explanation: AVG normalizes for differing order volumes across regions, providing a fairer comparison of typical order size than a raw total (SUM) would.

Quick Revision Points

• SUM totals a column's values per group; AVG computes the mean; MIN and MAX find the smallest and largest values per group.
• AVG and SUM both exclude NULL values from their calculations rather than treating them as zero.
• MIN and MAX apply to numeric, date, and text columns alike, unlike SUM and AVG which require numeric data.
• Combining multiple aggregate functions in one GROUP BY query is a standard, efficient way to build comprehensive reports.

Conclusion

• SUM, AVG, MIN, and MAX each answer a distinct type of numeric business question when combined with GROUP BY.
• Understanding how each function handles NULL values is essential for trusting the accuracy of a report.
• MIN and MAX extend usefully beyond numbers to dates and text, broadening their practical applications.
• Combining several aggregate functions in a single query is the standard way to build efficient, comprehensive summary reports.

SUM, AVG, MIN, and MAX combined with GROUP BY form the core toolkit for numeric business analytics in SQL: SUM computes totals, AVG computes averages, and MIN/MAX find the smallest and largest values within each group. All four exclude NULL values from their calculations rather than treating them as zero, and MIN/MAX extend usefully to date and text columns beyond just numbers. Combining several of these functions in a single GROUP BY query is the standard, efficient approach to building comprehensive sales and analytics reports.