SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 100;

1️⃣3️⃣ WHERE vs HAVING

This is one of the most frequently asked SQL interview questions.

WHERE filters individual rows. WHERE salary > 500000

HAVING filters groups. HAVING AVG(salary) > 800000

Remember:

WHERE → Filter rows

GROUP BY → Create groups

HAVING → Filter groups

1️⃣4️⃣ Example: WHERE + GROUP BY + HAVING

Requirement: Find departments whose average salary is greater than ₹8 lakh, considering only employees earning more than ₹5 lakh.

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
WHERE salary > 500000
GROUP BY department
HAVING AVG(salary) > 800000;

1️⃣5️⃣ GROUP BY With COUNT(DISTINCT)

Very useful for customer analytics.

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;

1️⃣6️⃣ GROUP BY With CASE

You can create business categories and then group them.

SELECT
    CASE
        WHEN salary >= 1000000 THEN 'High'
        WHEN salary >= 600000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_band,
    COUNT(*) AS employee_count
FROM employees
GROUP BY
    CASE
        WHEN salary >= 1000000 THEN 'High'
        WHEN salary >= 600000 THEN 'Medium'
        ELSE 'Low'
    END;

1️⃣7️⃣ GROUP BY Dates

This is extremely important for Data Analysts.

Example: Calculate monthly revenue.

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

1️⃣8️⃣ Daily Sales

SELECT
    order_date,
    SUM(amount) AS daily_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;

1️⃣9️⃣ Monthly Order Count

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

2️⃣0️⃣ Monthly Customer Count

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT customer_id) AS active_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Notice: COUNT(*) counts orders, while COUNT(DISTINCT customer_id) counts unique customers.

2️⃣1️⃣ GROUP BY With ORDER BY

Example: Find departments with the highest average salary.

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

2️⃣2️⃣ GROUP BY + HAVING + ORDER BY

A powerful analytical pattern:

SELECT
    customer_id,
    SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 50000
ORDER BY total_spend DESC;

2️⃣3️⃣ Real-World Example: Top Revenue Categories

Requirement: Find categories generating more than ₹10 lakh in revenue.

SELECT
    p.category,
    SUM(oi.quantity * oi.selling_price) AS revenue
FROM products p
JOIN order_items oi
    ON p.product_id = oi.product_id
GROUP BY p.category
HAVING SUM(oi.quantity * oi.selling_price) > 1000000
ORDER BY revenue DESC;

2️⃣4️⃣ Common GROUP BY Error

SELECT
    department,
    employee_name,
    AVG(salary)
FROM employees
GROUP BY department;