How to GROUP BY in MySQL

How do I use GROUP BY in MySQL to aggregate data?

GROUP BY aggregates rows that share common values into summary rows.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does GROUP BY do in MySQL?

GROUP BY collapses rows with identical values in one or more columns and returns one summary row per group. Combine it with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), or MAX() to produce totals and other metrics.

How do I write a basic GROUP BY query?

List the non-aggregated columns, add at least one aggregate, then append GROUP BY with the same non-aggregated columns.

SELECT customer_id, COUNT(*) AS order_count FROM Orders GROUP BY customer_id;

Which aggregate functions can I combine with GROUP BY?

Use SUM(), COUNT(), AVG(), MIN(), MAX(), GROUP_CONCAT(), JSON_ARRAYAGG(), or user-defined aggregates. Pick the function that answers your business question, e.g., SUM(total_amount) for revenue.

Can I GROUP BY multiple columns?

Yes. Include every column that defines a unique group.

SELECT customer_id, YEAR(order_date) AS yr, SUM(total_amount) AS yearly_spend FROM Orders GROUP BY customer_id, YEAR(order_date);

How do HAVING filters work with GROUP BY?

HAVING evaluates after grouping, letting you keep or discard groups based on aggregate values.

SELECT customer_id, SUM(total_amount) AS spend FROM Orders GROUP BY customer_id HAVING SUM(total_amount) > 5000;

What are best practices for GROUP BY performance?

Add indexes to columns used in GROUP BY and WHERE. Filter early with WHERE, avoid unnecessary columns, and consider summary tables or materialized views for heavy reports.

Example: Summarize revenue per customer

SELECT c.id, c.name, SUM(o.total_amount) AS total_spent FROM Customers AS c JOIN Orders AS o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY total_spent DESC LIMIT 10;

What mistakes should I avoid with GROUP BY?

Avoid selecting columns not listed in GROUP BY when ONLY_FULL_GROUP_BY is enabled, and don’t place HAVING conditions that belong in WHERE, as this forces unnecessary grouping.

Why How to GROUP BY in MySQL is important

How to GROUP BY in MySQL Example Usage


SELECT p.id,
       p.name,
       SUM(oi.quantity)        AS units_sold,
       SUM(oi.quantity * p.price) AS revenue
FROM Products      AS p
JOIN OrderItems    AS oi ON oi.product_id = p.id
GROUP BY p.id, p.name
HAVING SUM(oi.quantity) &gt; 50
ORDER BY revenue DESC;

How to GROUP BY in MySQL Syntax


SELECT column1, column2, aggregate_function(expression) AS alias
FROM table_name
[WHERE condition]
GROUP BY column1, column2
[HAVING aggregate_condition]
[ORDER BY column1 [ASC|DESC]]
[LIMIT n OFFSET m];

Common Mistakes

Selecting columns that are neither aggregated nor listed in GROUP BY when ONLY_FULL_GROUP_BY is active causes an error. Fix by aggregating the column or adding it to the GROUP BY list.
Placing filters that belong in WHERE inside HAVING makes MySQL group unnecessary rows first. Move non-aggregate conditions to WHERE to improve speed.

Frequently Asked Questions (FAQs)

Does GROUP BY sort the result automatically?

No. Use ORDER BY explicitly to control the output order. Without ORDER BY, MySQL may return groups in any sequence.

Why do I get an ONLY_FULL_GROUP_BY error?

MySQL’s ONLY_FULL_GROUP_BY SQL mode demands that every selected column be aggregated or listed in GROUP BY. Adjust the SELECT list or GROUP BY clause to comply.