employee_name is neither grouped, nor aggregated.2️⃣5️⃣ The Golden Rule of GROUP BY
When using GROUP BY, every selected expression generally needs to be either:
1. Included in GROUP BY
2. Or aggregated
Think: Group columns describe the group; aggregate functions summarize the group.
2️⃣6️⃣ SQL Query Pattern to Memorize
SELECT
grouping_column,
AGGREGATE_FUNCTION(value_column) AS metric
FROM table_name
WHERE row_condition
GROUP BY grouping_column
HAVING group_condition
ORDER BY metric DESC;
Example:
SELECT
city,
SUM(amount) AS revenue
FROM orders
WHERE order_status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 100000
ORDER BY revenue DESC;
🧠 Logical Processing Order
A useful simplified model is:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
This helps explain why
WHERE SUM(amount) > 100000 is not valid. Use HAVING instead.💼 SQL Interview Questions
Q1. What is GROUP BY?
Groups rows with the same values so aggregate functions can calculate metrics for each group.
Q2. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping, while HAVING filters groups after aggregation.
Q3. Can GROUP BY contain multiple columns?
Yes.
GROUP BY city, category;Q4. Can GROUP BY be used without an aggregate function?
Yes, although
SELECT DISTINCT is often clearer when the goal is simply to return unique combinations.Q5. Can you use aggregate functions in WHERE?
Generally no. Use HAVING.
Q6. Why do we use COUNT(DISTINCT customer_id)?
To count unique customers rather than counting every transaction.
🎯 Practice Questions
Q1. Count employees in each department.
Q2. Calculate total revenue by product category.
Q3. Calculate average salary by department.
Q4. Find the highest salary in each department.
Q5. Count customers by city.
Q6. Find cities with more than 500 customers.
Q7. Calculate monthly revenue.
Q8. Calculate monthly unique customers.
Q9. Find customers whose total spending is greater than ₹50,000.
Q10. Find product categories generating more than ₹1 lakh revenue, sorted from highest to lowest.
✅ Answers
Answer 1
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;
Answer 2
SELECT category, SUM(amount) AS revenue FROM sales GROUP BY category;
Answer 3
SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;
Answer 4
SELECT department, MAX(salary) AS highest_salary FROM employees GROUP BY department;
Answer 5
SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city;
Answer 6
SELECT city, COUNT(*) AS customer_count
FROM customers GROUP BY city HAVING COUNT(*) > 500;
Answer 7
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;Answer 8
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(DISTINCT customer_id) AS unique_customers FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;Answer 9
SELECT customer_id, SUM(amount) AS total_spend FROM orders GROUP BY customer_id HAVING SUM(amount) > 50000 ORDER BY total_spend DESC;
Answer 10
SELECT category, SUM(amount) AS revenue FROM sales GROUP BY category HAVING SUM(amount) > 100000 ORDER BY revenue DESC;