SELECT
    100.0 *
    SUM(
        CASE
            WHEN status = 'Success'
            THEN 1
            ELSE 0
        END
    ) / COUNT(*) AS success_rate
FROM transactions;

This combines: COUNT + SUM + CASE + Arithmetic.

2️⃣1️⃣ Why GROUP BY Comes Next

At the moment:

SELECT
    SUM(amount)
FROM orders;

gives you one total.

But what if the business asks:

What is the revenue for each city?

Now you need:

SELECT
    city,
    SUM(amount) AS revenue
FROM orders
GROUP BY city;

For now, understand the difference: Aggregate only ↓ One summary, GROUP BY + Aggregate ↓ One summary per group

2️⃣2️⃣ COUNT DISTINCT in Business Analytics

Suppose orders table has 5 orders, customer 101 appears twice, 103 appears twice. Total orders = COUNT(*) = 5, Unique customers = COUNT(DISTINCT customer_id) = 3. This distinction is fundamental.

2️⃣3️⃣ Common Mistake: COUNT(*) vs COUNT(DISTINCT)

If a customer places multiple orders: Customer 101 ↓ Order 1, Order 2, Order 3

Then: COUNT(*) counts: 3 while: COUNT(DISTINCT customer_id) counts: 1

2️⃣4️⃣ Real-World Dashboard Query

Imagine your manager asks for a quick sales summary.

SELECT
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order_value,
    MIN(amount) AS minimum_order,
    MAX(amount) AS maximum_order
FROM orders
WHERE order_status = 'Completed';

This gives you six useful business metrics in one query.

🧠 Common Beginner Mistakes

❌ Mistake 1: Counting the wrong thing.

Don't automatically use: COUNT(*) when the requirement says:

Number of customers. Use: COUNT(DISTINCT customer_id) when appropriate.

❌ Mistake 2: Assuming NULL is zero.

NULL → Missing/unknown, 0 → Actual numeric zero

❌ Mistake 3: Using SUM on text.

SUM() is designed for numeric expressions. This is invalid or inappropriate: SUM(customer_name)

❌ Mistake 4: Forgetting the business definition.

"Revenue" might mean: Gross revenue, Net revenue, Completed-order revenue, Revenue after discounts, Revenue excluding refunds. Always understand the business definition before writing the SQL.

💼 SQL Interview Questions

Q1. What is an aggregate function?

An aggregate function performs a calculation over multiple rows and returns a summarized value.

Q2. Name five common aggregate functions. COUNT(), SUM(), AVG(), MIN(), MAX()

Q3. Difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column.

Q4. What does COUNT(DISTINCT customer_id) do?

It counts the number of unique non-NULL customer IDs.

Q5. Does AVG ignore NULL values?

Yes, AVG() normally ignores NULL values.

Q6. How do you calculate total revenue?

SELECT SUM(amount) FROM orders;

Q7. How do you find the highest salary?

SELECT MAX(salary) FROM employees;

Q8. Can multiple aggregate functions be used together?

Yes.

🎯 Practice Questions

Q1. Find the total number of employees.

Q2. Find the average employee salary.

Q3. Find the highest product price.

Q4. Find the lowest product price.

Q5. Calculate total revenue from completed orders.

Q6. Count the number of unique customers who placed an order.

Q7. Find the largest order amount.

Q8. Calculate the average order value for completed orders.

Q9. Count the number of completed orders.

Q10. Calculate total revenue and total unique customers from completed orders.

✅ Answers

Answer 1