SQL Programming Resources on Telegram: “1️⃣6️⃣ Calculate Success Rate You can combine CA…”

1️⃣6️⃣ Calculate Success Rate

You can combine CASE, SUM, and COUNT.

SELECT
    ROUND(
        100.0 *
        SUM(
            CASE
                WHEN order_status = 'Completed'
                THEN 1
                ELSE 0
            END
        ) / NULLIF(COUNT(*), 0),
        2
    ) AS completion_rate
FROM orders;

The logic:

• Completed orders ÷ Total orders × 100

1️⃣7️⃣ Conditional Revenue

Suppose you want only revenue from completed orders.

SELECT
    SUM(
        CASE
            WHEN order_status = 'Completed'
            THEN amount
            ELSE 0
        END
    ) AS completed_revenue
FROM orders;

This is especially useful when you need several conditional metrics in one query.

1️⃣8️⃣ Multiple Conditional Metrics

You can build an entire KPI summary:

SELECT
    COUNT(*) AS total_orders,

    SUM(
        CASE
            WHEN order_status = 'Completed'
            THEN 1 ELSE 0
        END
    ) AS completed_orders,

    SUM(
        CASE
            WHEN order_status = 'Cancelled'
            THEN 1 ELSE 0
        END
    ) AS cancelled_orders,

    SUM(
        CASE
            WHEN order_status = 'Completed'
            THEN amount ELSE 0
        END
    ) AS completed_revenue,

    SUM(
        CASE
            WHEN order_status = 'Cancelled'
            THEN amount ELSE 0
        END
    ) AS cancelled_value
FROM orders;

This is very close to the kind of SQL used behind BI dashboards.

1️⃣9️⃣ CASE With GROUP BY

You can create categories and then aggregate them.

Example:

SELECT
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END AS order_size,
    COUNT(*) AS order_count
FROM orders
GROUP BY
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END;

Result:

order_size | order_count
Large      | 120
Medium     | 450
Small      | 980

2️⃣0️⃣ CASE + GROUP BY + SUM

You can also calculate revenue by order category.

SELECT
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END AS order_size,
    SUM(amount) AS revenue
FROM orders
GROUP BY
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END;

2️⃣1️⃣ Simple CASE vs Searched CASE

There are two common forms.

Searched CASE

This is what we've mainly used:

CASE
    WHEN salary >= 1000000 THEN 'High'
    WHEN salary >= 600000 THEN 'Medium'
    ELSE 'Low'
END

It evaluates conditions.

Simple CASE

Useful when comparing one expression against specific values:

CASE department
    WHEN 'IT' THEN 'Technology'
    WHEN 'HR' THEN 'People'
    WHEN 'Finance' THEN 'Corporate'
    ELSE 'Other'
END

Think:

• Simple CASE → Compare one value

• Searched CASE → Evaluate different conditions

2️⃣2️⃣ CASE and NULL

You can explicitly handle NULL.

SELECT
    employee_name,
    CASE
        WHEN manager_id IS NULL
            THEN 'No Manager Assigned'
        ELSE 'Manager Assigned'
    END AS manager_status
FROM employees;

This is much better than comparing NULL using =.

2️⃣3️⃣ CASE and COALESCE

Sometimes you want to replace NULL with a default value.

SELECT
    employee_name,
    COALESCE(bonus, 0) AS bonus
FROM employees;

You can combine this with CASE:

SELECT
    employee_name,
    CASE
        WHEN COALESCE(bonus, 0) > 10000
            THEN 'High Bonus'
        ELSE 'Standard Bonus'
    END AS bonus_category
FROM employees;