Beginner LevelSQL & MySQL
How do GROUP BY and HAVING clauses work alongside aggregate functions (COUNT, SUM, AVG)?
❓ Interview Prompt: Explain SQL Aggregations, GROUP BY execution, HAVING filter clauses, and standard aggregate functions (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`).
SQL aggregation collapses multiple table rows into summary output rows:
Grouping & Filtering Rules:
•
'GROUP BY': Groups rows containing identical values in specified columns.
•
Aggregate Functions: 'COUNT()', 'SUM()', 'AVG()', 'MIN()', 'MAX()' compute calculations per group.
•
'HAVING' Clause: Filters aggregated groups based on result calculations (e.g. 'HAVING COUNT(*) > 5'). Cannot use 'WHERE' to filter aggregate values!
Code Implementation Example
sql
SELECT category_id, COUNT(*) AS total_products, AVG(price) AS avg_price
FROM products
WHERE status = 'PUBLISHED'
GROUP BY category_id
HAVING COUNT(*) >= 10 AND AVG(price) > 50.00
ORDER BY avg_price DESC;💡Grouping products by category and filtering aggregates using HAVING.
🎯 Interview Key Takeaway
Use WHERE to filter individual rows before grouping, and HAVING to filter aggregated group results.
#SQL#GROUP BY#HAVING#Aggregations#Beginner
Back to all SQL & MySQL Questions→