📊 Data Aggregation = Business Intelligence
Raw data is not enough. GROUP BY and HAVING aggregate data for business intelligence and reporting.
📝 Aggregation Examples
-- Basic GROUP BY
SELECT
category,
COUNT(*) as product_count,
AVG(price) as avg_price,
SUM(stock) as total_stock,
MIN(price) as min_price,
MAX(price) as max_price
FROM products
GROUP BY category;
-- Multiple Columns
SELECT
year,
month,
SUM(sales) as total_sales,
COUNT(*) as order_count
FROM orders
GROUP BY year, month
ORDER BY year DESC, month DESC;
-- HAVING Clause
SELECT
category,
COUNT(*) as product_count,
AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100
AND COUNT(*) > 5
ORDER BY avg_price DESC;
-- WITH ROLLUP
SELECT
category,
subcategory,
SUM(sales) as total_sales
FROM products
GROUP BY ROLLUP(category, subcategory);
-- WITH CUBE
SELECT
category,
subcategory,
SUM(sales) as total_sales
FROM products
GROUP BY CUBE(category, subcategory);
🎯 Advanced Aggregation
-- Window Functions with Aggregation
SELECT
category,
product_name,
price,
AVG(price) OVER (PARTITION BY category) as category_avg,
price - AVG(price) OVER (PARTITION BY category) as price_diff
FROM products;
-- Pivot Table
SELECT
year,
SUM(CASE WHEN month = 1 THEN sales END) as jan,
SUM(CASE WHEN month = 2 THEN sales END) as feb,
SUM(CASE WHEN month = 3 THEN sales END) as mar
FROM sales
GROUP BY year;
-- Aggregate with Subquery
SELECT
category,
COUNT(*) as count,
(SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category) as avg_price
FROM products p1
GROUP BY category;
-- Percentile Aggregation
SELECT
category,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) as median_price,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) as q1_price,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) as q3_price
FROM products
GROUP BY category;
💡 Aggregation Tips
- Use appropriate aggregate functions
- Filter with HAVING instead of WHERE
- Use window functions for running totals
- Consider performance for large datasets
- Use indexes on GROUP BY columns
Data aggregation transforms raw data into actionable insights. It’s the foundation of business intelligence.

