Course contentsShow
← Data Science
Lesson 892 of 2,14523. SQL Aggregation and GroupingFree lesson

GROUP BY with Different Aggregate Functions

Using COUNT, SUM, AVG, MIN, and MAX with GROUP BY to summarize groups in various ways.

What You'll Learn

You'll learn how to use multiple aggregate functions—COUNT, SUM, AVG, MIN, and MAX—together with GROUP BY to create rich summaries of grouped data.

The Core Idea

Once you've grouped rows using GROUP BY, you're not limited to just one aggregate function. You can apply different aggregate functions to different columns in the same query, building a multi-dimensional summary of each group. This is like getting a complete report card for each category: not just the count of students, but also their average score, highest score, lowest score, and total points earned—all in one view.

Combining Aggregates in One Query

When you combine multiple aggregates with GROUP BY, each function operates independently on its specified column within each group:

SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

This query groups employees by department and calculates five different metrics for each department simultaneously. You get a comprehensive snapshot of each group in a single result set.

Mixing Column Types

You can apply different aggregates to different columns based on what makes sense:

SELECT 
    product_category,
    COUNT(*) AS products_sold,
    SUM(quantity) AS total_units,
    AVG(price) AS avg_price,
    MAX(sale_date) AS most_recent_sale
FROM sales
GROUP BY product_category;

Here, COUNT tallies rows, SUM adds quantities, AVG computes average price, and MAX finds the latest date—all per category.

Key Takeaway: Combining multiple aggregate functions with GROUP BY lets you generate comprehensive, multi-metric summaries of each group in a single query, turning raw data into actionable insights.