Calculating Percentages and Ratios

In this lesson, you will learn how to compare a specific subset of data against the entire population to calculate meaningful percentages and ratios directly within your SQL queries.

The Logic of Part-to-Whole

To calculate a percentage, you need two numbers: the part (the subset you are interested in) and the whole (the total count of all records).

In SQL, we can calculate these simultaneously by combining conditional logic with aggregate functions. A common pattern is to use a CASE statement inside a SUM() function. By checking a condition and returning 1 if it is met (and NULL or 0 otherwise), you can "count" only the rows that match your criteria.

Calculating a Percentage

Imagine we have a table called orders and we want to find out what percentage of orders were "completed." We can divide the count of completed orders by the total count of all orders.

SELECT 
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS completion_percentage
FROM orders;

Here is what is happening under the hood:

1. The Numerator: SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) isolates only the completed orders and adds them up.
2. The Denominator: COUNT(*) counts every row in the table, giving us our total population.
3. The Multiplier: We multiply by 100.0 to convert the ratio into a percentage. We use 100.0 (with a decimal) because many SQL databases perform "integer division." If you divide 5 by 10 using integers, the database might return 0 instead of 0.5. Using 100.0 forces the database to use decimal math.

Calculating a Ratio

A ratio is essentially the same process, just without the * 100 multiplier. For example, if you wanted to see the ratio of "cancelled" orders to "completed" orders, you would compare two SUM(CASE...) results rather than comparing a subset to the total.

This technique is powerful because it allows you to see how small groups fit into the bigger picture without needing to pull all the raw data into an external spreadsheet program.

Key Takeaway: To calculate a percentage, divide the sum of your filtered subset by the total count of rows, ensuring you use decimal numbers to maintain precision during division.