MySQL GROUP BY

MySQL GROUP BY Clause: Data Aggregation

The GROUP BY clause groups rows sharing identical column values into summary rows. It is used with aggregate functions (COUNT(), SUM(), AVG(), MIN(), MAX()).

Aggregate Execution Lifecycle

flowchart TD
    A["Raw Table Rows"] --> B["Filter Rows (WHERE)"]
    B --> C["Group Rows (GROUP BY)"]
    C --> D["Calculate Aggregates (SUM, AVG)"]
    D --> E["Filter Groups (HAVING)"]

Practical SQL Code Example

-- Calculate total revenue per product category
SELECT 
    category, 
    COUNT(id) AS total_products, 
    SUM(sales_amount) AS category_revenue
FROM sales
GROUP BY category;

Expected Output

Best Practices & Gotchas

• ONLY_FULL_GROUP_BY Mode: MySQL 8.0 enforces standard SQL. All non-aggregated columns in SELECT must be explicitly included in GROUP BY.
• WHERE vs HAVING: Use WHERE to filter raw rows before grouping; use HAVING to filter aggregated values after grouping.

Self-Check Challenge

What is the operational difference between the WHERE clause and the HAVING clause?