Aggregations & Grouping: Data Analysis

SQL isn't just for retrieval; it's a powerful analytical tool. Aggregation functions allow you to summarize large datasets into meaningful insights.

Common Aggregate Functions

• COUNT(): Number of rows.
• SUM(): Total of numeric values.
• AVG(): Average of numeric values.
• MIN() / MAX(): Smallest/Largest values.

sql code
SELECT COUNT(*) as total_orders, SUM(amount) as total_revenue
FROM orders
WHERE status = 'completed';

Mastering GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows. It is almost always used with aggregate functions.

sql code
SELECT category, AVG(price) as average_price
FROM products
GROUP BY category;

Filtering Groups: HAVING vs WHERE

• WHERE filters rows before aggregation.
• HAVING filters groups after aggregation.

sql code
-- List categories with an average price greater than 500
SELECT category, AVG(price) as avg_p
FROM products
GROUP BY category
HAVING AVG(price) > 500;

The "Non-Aggregated Column" Error

In most SQL dialects (except older MySQL), every column in your SELECT must either be an aggregate function or part of the GROUP BY clause.