GROUP BY organizes rows into groups based on column values and lets you perform calculations on each group.

Key Concepts:

• Groups rows with same values
• Works with aggregate functions
• Creates summary data
• Reduces row count

Common Aggregate Functions:

• COUNT() - Number of rows
• SUM() - Total of values
• AVG() - Average of values
• MAX() - Highest value
• MIN() - Lowest value

Basic Syntax:

SELECT column1, AGG_FUNCTION(column2)
FROM table
GROUP BY column1;

Let's start with a simple grouping to count orders by customer.

Write a query that gets:

• Customer name
• Count of their orders
• From customers and orders tables
• Grouped by customer
• Ordered by order count (highest first)

Tips:

• JOIN before GROUP BY
• Select grouping columns
• Use COUNT for totals
• Consider NULL handling

We can use multiple aggregate functions in the same query.

Write a query that shows:

• Category name
• Number of products
• Average price
• Total value (sum of prices)
• Minimum and maximum prices
• Grouped by category

Best Practices:

• Use clear column aliases
• Consider decimal precision
• Handle NULL values
• Order results meaningfully

HAVING filters groups after aggregation, unlike WHERE which filters rows before.

Write a query that finds:

• Categories with more than 5 products
• Show category name and product count
• Include average price
• Order by product count

Key Differences:

• WHERE filters rows
• HAVING filters groups
• HAVING uses aggregates
• Executes after GROUP BY

Note: HAVING is often used with COUNT, AVG, SUM.

Group by multiple columns to create more detailed summaries.

Write a query that analyzes:

• Orders by category and month
• Show category name
• Extract month from order date
• Count orders and total revenue
• Order by category and month

Advanced Concepts:

• Multiple GROUP BY columns
• Date functions
• Complex aggregations
• Hierarchical grouping

Combine GROUP BY with other SQL features for advanced analysis.

Write a query that:

• Groups customers by country
• Shows number of customers
• Shows total orders and revenue
• Only countries with >$1000 revenue
• Orders by total revenue

This combines:

• Multiple joins
• Aggregate functions
• HAVING clause
• Complex ordering
• NULL handling

Create a comprehensive sales analysis report.

Write a query that shows:

• Category name
• Total products
• Products sold (with orders)
• Sales percentage (products sold / total)
• Total revenue
• Average order value

This demonstrates:

• Complex calculations
• Multiple aggregates
• Percentage calculations
• Business metrics
• Professional reporting