MySQL GROUP BY Aggregate Functions

GROUP BY with Joins

When joining tables, grouping can accidentally count multiplied rows. For example, orders joined to order_items returns one row per item, not one row per order. Use COUNT(DISTINCT orders.id) when the report needs unique orders.

Know the row shape after each join.
Use DISTINCT only when it matches the question.
Inspect raw joined rows before aggregating.

Date Grouping for Reports

Dashboards often group by DATE(created_at), MONTH(created_at), or YEAR(created_at). This is useful, but wrapping columns in functions can affect index usage, so date ranges in WHERE still matter.

Filter by date range before grouping.
Alias date expressions clearly.
Consider summary tables for heavy reports.

ONLY_FULL_GROUP_BY Discipline

MySQL strict mode prevents selecting random non-grouped columns. Treat this as helpful: every selected value should either identify the group or summarize it.

Group by dimensions.
Aggregate measures.
Avoid accidental non-deterministic columns.

Group Deterministically Under ONLY_FULL_GROUP_BY

Every selected expression must be aggregated, named in the grouping set, or functionally dependent on grouped columns under MySQL rules. Selecting an arbitrary label beside an aggregate can return indeterminate data when permissive SQL modes are used. Keep ONLY_FULL_GROUP_BY enabled and express the intended row with an aggregate, a join, or a window function.

WHERE filters source rows before grouping; HAVING filters completed groups. Moving a condition between them can change both results and work performed. Treat NULL explicitly because COUNT(*) counts rows, COUNT(column) ignores NULL values, and aggregates over no matching rows may return NULL.

Report Subtotals Without Losing Detail Rules

WITH ROLLUP can add subtotal and grand-total rows, but the application must distinguish those rows from a genuine NULL grouping value. GROUPING identifies whether NULL was produced by the rollup. Label subtotal rows after making that distinction.

A window aggregate is often better when each detail row must remain visible. Compare EXPLAIN plans and indexes for the join, filter, grouping, and order columns with realistic cardinality. A covering index can reduce work, but an index that begins with a low-selectivity grouping column may not help the filter.

Sales Summary by Status

SELECT
  status,
  COUNT(*) AS total_orders,
  SUM(amount) AS total_revenue,
  AVG(amount) AS average_order_value
FROM orders
WHERE payment_status = 'paid'
GROUP BY status
ORDER BY total_revenue DESC;

HAVING After Grouping

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 10000
ORDER BY lifetime_value DESC;

Count Unique Orders After Join

SELECT
  customers.id,
  customers.name,
  COUNT(DISTINCT orders.id) AS order_count,
  SUM(order_items.quantity * order_items.price) AS revenue
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN order_items ON order_items.order_id = orders.id
GROUP BY customers.id, customers.name;

Daily Revenue Report

SELECT
  DATE(created_at) AS sales_day,
  COUNT(*) AS orders_count,
  SUM(amount) AS revenue
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-02-01'
GROUP BY DATE(created_at)
ORDER BY sales_day;

Before you move on