How does GROUP BY work in SQL and when should it be used?
💡 Concept Name
GROUP BY is an SQL clause used to group rows that have the same values in specified columns and perform aggregate functions like COUNT, SUM, AVG, etc., on those groups.
📘 Quick Intro
GROUP BY is used to collapse many rows into a single summary row for each unique value in the specified column(s). It’s especially useful in reporting, dashboards, and aggregations.
🧠 Analogy / Short Story
Imagine organizing your monthly expenses by category—food, transport, entertainment. GROUP BY is like sorting your receipts into labeled folders and then calculating the total spend in each. It helps you summarize large data into understandable segments.
🔧 Technical Explanation
- GROUP BY groups rows based on unique values in one or more columns.
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX) are applied to each group.
- All selected columns must either be part of the GROUP BY clause or used in an aggregate function.
- GROUP BY can be combined with HAVING to filter aggregated results.
- ORDER BY can be used afterward to sort grouped results.
🎯 Purpose & Use Case
- ✅ Get the number of orders per customer.
- ✅ Calculate total sales per region or product.
- ✅ Identify average salary per department.
- ✅ Analyze performance by grouping test scores by student.
💻 Real Code Example
-- Count orders per customer
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID;
-- Total sales per product
SELECT ProductID, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY ProductID;
❓ Interview Q&A
Q1: What is the purpose of GROUP BY?
A: To group rows with the same values and apply aggregate functions to each group.
Q2: Can you use GROUP BY without an aggregate function?
A: Technically yes, but it’s rarely useful unless combined with aggregate functions.
Q3: What is the difference between WHERE and HAVING?
A: WHERE filters rows before grouping; HAVING filters groups after aggregation.
Q4: Can you group by multiple columns?
A: Yes, GROUP BY can group by two or more columns to create subgroups.
Q5: What happens if you select a column not in GROUP BY or an aggregate?
A: SQL throws an error; all selected fields must be grouped or aggregated.
Q6: Is GROUP BY used with joins?
A: Yes, it’s common to join tables and then group the combined results.
Q7: How do you count distinct values within groups?
A: Use COUNT(DISTINCT column) within the GROUP BY query.
Q8: What’s the default order of grouped results?
A: GROUP BY does not sort; use ORDER BY explicitly if needed.
Q9: Can GROUP BY be used in subqueries?
A: Yes, it can summarize results that are later joined or filtered.
Q10: Is GROUP BY available in all SQL dialects?
A: Yes, though syntax may vary slightly in advanced features across systems.
📝 MCQs
Q1. What does GROUP BY do in SQL?
- Sorts data
- Filters data
- Groups rows for aggregation
- Deletes duplicates
Q2. Which function is commonly used with GROUP BY?
- DROP()
- SELECT TOP
- SUM()
- CREATE TABLE
Q3. What clause filters results after aggregation?
- WHERE
- ORDER BY
- HAVING
- GROUP BY
Q4. Which clause should follow GROUP BY for sorting?
- HAVING
- ORDER BY
- SELECT
- DISTINCT
Q5. Can GROUP BY be used on multiple columns?
- No
- Yes
- Only in MySQL
- Only in joins
Q6. What does COUNT(*) return?
- Row ID
- Max row ID
- Total rows in group
- Average of group
Q7. When must a column be in GROUP BY?
- Never
- Always
- Only in ORDER BY
- If it appears in SELECT and isn’t aggregated
Q8. Which clause groups data?
- WHERE
- JOIN
- GROUP BY
- HAVING
Q9. What is true about GROUP BY and SELECT?
- GROUP BY must be first
- HAVING comes before SELECT
- All selected columns must be aggregated or grouped
- Only use on numeric columns
Q10. Can GROUP BY be used in subqueries?
- No
- Yes
- Only with HAVING
- Only for joins
💡 Bonus Insight
GROUP BY doesn’t automatically sort results; if you need sorted output, always use ORDER BY after it. Also, combining GROUP BY with window functions enables powerful analytics.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!