DevNotes
Support
SQL & MySQL · Chapter 13

GROUP BY and Aggregate Functions

Summarizing millions of rows into business answers — COUNT, SUM, AVG, MIN, MAX, grouping by several columns, COUNT(*) vs COUNT(column), NULL behaviour and ONLY_FULL_GROUP_BY.

◷ 10 min read§ 20 sections? 8 interview questions{ } 28 code examples

#Introduction

This chapter begins the aggregation and reporting phase — where SQL becomes extremely powerful for business reports, dashboards, revenue analytics, sales summaries, department reports, booking statistics and backend reporting APIs.

A 5+ years Java backend developer should be comfortable writing queries such as total revenue per product, average salary per department, orders per customer, daily booking counts, monthly sales and the highest transaction per user.

#What is GROUP BY?

GROUP BY combines rows that have the same value into logical groups.

idnamedepartmentsalary
1JohnIT70000
2DavidIT90000
3LisaHR50000
4AlexHR55000
5MaryFinance80000
6TomFinance85000

SELECT * FROM employees; returns six individual rows. With:

SQL
SELECT department
FROM employees
GROUP BY department;
Output
IT
HR
Finance

Instead of six rows, there's one row per distinct department:

IT
JohnDavid
HR
LisaAlex
Finance
MaryTom

The real power of GROUP BY appears when it's combined with aggregate functions.

#Why Do We Need Aggregation?

Your manager asks: how many employees are in each department? You don't want the list John, David, Lisa, Alex, Mary, Tom — you want IT → 2, HR → 2, Finance → 2. That's aggregation: many rows summarized into meaningful business results.

#Aggregate Functions

Aggregate functions calculate a value across multiple rows. The five most important:

FunctionPurpose
COUNT()Count rows or non-NULL values
SUM()Calculate a total
AVG()Calculate an average
MIN()Find the minimum value
MAX()Find the maximum value

#COUNT()

SQL
SELECT COUNT(*)
FROM employees;          -- 6

Employees in Each Department

SQL
SELECT department,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department;
DepartmentEmployee count
IT2
HR2
Finance2
GROUP BY department
Create department groups
COUNT rows inside each group

#SUM()

SQL
SELECT SUM(salary) AS total_salary
FROM employees;          -- 430000

SELECT department,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department;
DepartmentTotal salary
IT160000
HR105000
Finance165000

(IT = 70000 + 90000 = 160000.)

#AVG()

SQL
SELECT AVG(salary) AS average_salary
FROM employees;          -- 71666.67

SELECT department,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department;
DepartmentAverage salary
IT80000
HR52500
Finance82500

#MAX() and MIN()

SQL
SELECT MAX(salary) AS highest_salary
FROM employees;          -- 90000

SELECT department,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM employees
GROUP BY department;
DepartmentHighest salaryLowest salary
IT9000070000
HR5500050000
Finance8500080000

#Multiple Aggregates Together

Extremely common in reporting APIs:

SQL
SELECT department,
       COUNT(*)    AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary,
       MIN(salary) AS lowest_salary,
       MAX(salary) AS highest_salary
FROM employees
GROUP BY department;
DepartmentCountSumAvgMinMax
IT2160000800007000090000
HR2105000525005000055000
Finance2165000825008000085000

This is exactly how many reporting and dashboard APIs are built — a department report with employee count, total, average, minimum and maximum salary.

#Real Business Examples

Customer-Wise Spending

OrderCustomerAmount
1A100
2B250
3A400
4B300
5C150
SQL
SELECT customer,
       SUM(amount) AS total_spent
FROM orders
GROUP BY customer;
CustomerTotal spent
A500
B550
C150

Sales, Revenue and Other Reports

SQL
-- Total quantity sold for each product
SELECT product_id,
       SUM(quantity) AS total_sold
FROM sales
GROUP BY product_id;

-- Total revenue generated by each product
SELECT product_id,
       SUM(quantity * price) AS revenue
FROM sales
GROUP BY product_id;

The expression quantity * price is calculated for each row; SUM(...) then adds those values within each product group.

SQL
-- Daily revenue
SELECT order_date,
       SUM(total_amount) AS daily_revenue
FROM orders
GROUP BY order_date;

-- Department salary report
SELECT department,
       COUNT(*)    AS employee_count,
       AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

-- Orders per customer
SELECT customer_id,
       COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

#GROUP BY Multiple Columns

DepartmentGenderSalary
ITM70000
ITF90000
HRM50000
HRF55000
ITM80000

Count employees for every department + gender combination:

SQL
SELECT department,
       gender,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department, gender;
DepartmentGenderCount
ITM2
ITF1
HRM1
HRF1

The grouping key is now department + gender — each unique combination becomes a separate group.

#COUNT(*) vs COUNT(column)

A common interview question.

idnamebonus
1Rahul1000
2AmitNULL
3Neha2000
SQL
SELECT COUNT(*)     FROM employees;   -- 3: counts rows
SELECT COUNT(bonus) FROM employees;   -- 2: ignores NULL values
ExpressionCounts
COUNT(*)Rows
COUNT(column)Non-NULL values
COUNT(DISTINCT column)Unique non-NULL values
SQL
SELECT COUNT(DISTINCT department) AS department_count
FROM employees;   -- IT, IT, HR, Finance, Finance → 3

#NULL Behaviour in Aggregate Functions

Aggregate functions generally ignore NULL. With salaries 10000, 20000, NULL:

QueryResult
COUNT(*)3
COUNT(salary)2
SUM(salary)30000
AVG(salary)15000

Why not 10000? Because AVG() ignores NULL: (10000 + 20000) ÷ 2 non-NULL values = 15000. This behaviour is extremely important in production reporting (use AVG(COALESCE(salary, 0)) if NULL should count as zero).

#SQL Logical Execution Order

SQL
SELECT department,
       AVG(salary) AS average_salary
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING AVG(salary) > 70000
ORDER BY average_salary DESC
LIMIT 5;
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

The most important distinction:

WHEREHAVING
Filters individual rowsFilters groups
Before groupingAfter grouping

HAVING is covered in depth in the next chapter.

#Common Mistakes

Mistake 1 — Forgetting GROUP BY

SQL
SELECT department, COUNT(*)
FROM employees;     -- ❌ with ONLY_FULL_GROUP_BY

With ONLY_FULL_GROUP_BY enabled (MySQL 5.7+ default), this is rejected: department is a non-aggregated column next to the aggregate COUNT(*). Correct:

SQL
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Mistake 2 — Selecting Non-Grouped Columns

SQL
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;     -- ❌

The IT group contains John and David — which name should MySQL return? The query doesn't say. With ONLY_FULL_GROUP_BY, MySQL rejects such ambiguous queries unless the column is functionally dependent on the grouped columns (e.g. grouped by the primary key).

Mistake 3 — Using WHERE with Aggregate Functions

SQL
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 2          -- ❌
GROUP BY department;

WHERE runs before GROUP BY, when COUNT(*) hasn't been evaluated per group yet. Use HAVING:

SQL
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Mistake 4 — Confusing COUNT(*) and COUNT(column)

COUNT(*) counts rows; COUNT(column) counts non-NULL values. They can return different results.

Mistake 5 — Forgetting That AVG() Ignores NULL

With salaries 10000, 20000 and NULL, AVG(salary) returns 15000, not 10000.

#Interview Questions

Q1Count employees in every department.
SQL
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Q2Find the highest salary in each department.
SQL
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;
Q3Find total revenue per product.
SQL
SELECT product_id, SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;
Q4Find the number of orders placed by each customer.
SQL
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
Q5Find the average order amount per customer.
SQL
SELECT customer_id, AVG(total_amount) AS average_order_amount
FROM orders
GROUP BY customer_id;
Q6What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after grouping.

Q7What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column.

Q8Can GROUP BY use multiple columns?

Yes — GROUP BY department, gender; each unique combination becomes a separate group.

#Mini Project

Order IDCustomerProductAmount
1ALaptop50000
2AMouse1000
3BLaptop50000
4CKeyboard2000
5BMouse1000
6AKeyboard2000
SQL
-- 1. Total amount spent by each customer → A 53000, B 51000, C 2000
SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

-- 2. Number of orders for each product → Laptop 2, Mouse 2, Keyboard 2
SELECT product, COUNT(*) AS order_count
FROM orders
GROUP BY product;

-- 3. Total revenue for each product → Laptop 100000, Mouse 2000, Keyboard 4000
SELECT product, SUM(amount) AS total_revenue
FROM orders
GROUP BY product;

-- 4. Average order amount per customer
SELECT customer, AVG(amount) AS average_order_amount
FROM orders
GROUP BY customer;

-- 5. Highest order amount placed by each customer
SELECT customer, MAX(amount) AS highest_order_amount
FROM orders
GROUP BY customer;

-- 6. Number of orders placed by each customer
SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer;

#Practice Problems

Easy

  1. Count employees in each department.
  2. Find the average salary per department.
  3. Find the highest salary per department.
  4. Find the lowest salary per department.
  5. Find the total salary paid in each department.

Medium

  1. Count orders placed by each customer.
  2. Calculate total sales for each product.
  3. Find the average product price by category.
  4. Find monthly revenue using YEAR(order_date) and MONTH(order_date).
  5. Find the total quantity sold for each product.
SQL
SELECT YEAR(order_date)  AS order_year,
       MONTH(order_date) AS order_month,
       SUM(total_amount) AS monthly_revenue
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

Including the year prevents January of different years from being grouped together.

Interview level

  1. Find the top 3 departments by total salary.
  2. Find the customer with the highest total spending.
  3. Find the product generating the highest revenue.
  4. Find the department with the highest average salary.
  5. Generate a dashboard showing COUNT, SUM, AVG, MIN and MAX for each department.

#Real Backend Example

A Spring Boot API exposes GET /api/dashboard/sales, needing total orders, total revenue, and average/minimum/maximum order value:

SQL
SELECT COUNT(*)          AS total_orders,
       SUM(total_amount) AS total_revenue,
       AVG(total_amount) AS average_order_value,
       MIN(total_amount) AS minimum_order_value,
       MAX(total_amount) AS maximum_order_value
FROM orders;

Customer-wise statistics:

SQL
SELECT customer_id,
       COUNT(*)          AS total_orders,
       SUM(total_amount) AS total_spent,
       AVG(total_amount) AS average_order_value
FROM orders
GROUP BY customer_id;

This is the foundation of SQL-based reporting APIs.

#Cheat Sheet

ExpressionMeaning
COUNT(*)Counts rows
COUNT(column)Counts non-NULL values
COUNT(DISTINCT column)Counts unique non-NULL values
SUM(salary)Total
AVG(salary)Average of non-NULL values
MIN(salary) / MAX(salary)Lowest / highest value
GROUP BY department, genderOne group per unique combination

Interview tips:

  • GROUP BY creates groups; aggregate functions calculate a value for each group.
  • Master all five aggregates: COUNT, SUM, AVG, MIN, MAX.
  • Understand COUNT(*) vs COUNT(column) and how aggregates handle NULL.
  • WHERE filters rows before grouping; HAVING filters groups after aggregation.
  • Don't select arbitrary non-grouped columns.
  • Include the year when grouping by month across multiple years.