Step by Step Guide to Calculating and Formatting Percentages in SQL Server

When you’re writing reports in SQL Server, one of the first little annoyances you’ll probably bump into is how percentages show up. By default, SQL Server doesn’t have a built-in “percent” data type. Percentages are usually stored as decimals (for example, 0.25 for 25%), and if you just throw those into a report, they won’t look the way people expect.

So you’ll need to do a bit of work to get it nicely formatted into a percentage format that people expect to see.

Also, if you’re calculating percentages from raw values then that will require some more work.

In this article we’ll walk through an example of how to calculate and format percentages in SQL Server.

Example

If you just want some quick copy/paste code, jump to either step 3 or step 4, (depending on your requirements). Otherwise feel free to walk through each code snippet to understand what each part of the code does.

Sample Data

First we’ll start by loading some data. Suppose you’re tracking how many items in a batch passed quality checks. You have a table like this:

CREATE TABLE BatchResults (
    BatchID INT,
    ItemsChecked INT,
    ItemsPassed INT
);

INSERT INTO BatchResults (BatchID, ItemsChecked, ItemsPassed)
VALUES
(1, 100, 95),
(2, 200, 176),
(3, 150, 123);

The ItemsChecked column tells us how many items were checked, while the ItemsPassed column tells us how many passed that check. We can therefore calculate the percentage of items that passed each check (and then format it as a user-friendly percentage).

Step 1: Calculating the Percentage

If you want to calculate the pass rate from the above data, you might start with:

SELECT 
    BatchID,
    CAST(ItemsPassed AS DECIMAL(10,2)) / ItemsChecked AS PassRate
FROM BatchResults;

Output:

BatchID  PassRate
-------  --------
1        0.95    
2        0.88    
3        0.82    

These values are correct mathematically, but in a report, people usually expect to see 95%, 88%, and 82%.

Step 2: Converting Decimal to Percentage

The first thing we need to do is multiply the value by 100:

SELECT 
    BatchID,
    CAST((CAST(ItemsPassed AS DECIMAL(10,2)) / ItemsChecked) * 100 AS DECIMAL(5,2)) 
        AS PassRatePercent
FROM BatchResults;

Output:

BatchID  PassRatePercent
-------  ---------------
1        95             
2        88             
3        82             

Better, but it still doesn’t include the % sign.

Step 3: Adding the Percentage Sign

To format it with the percent sign, you need to turn the number into text and concatenate %.

SELECT 
    BatchID,
    STR((CAST(ItemsPassed AS DECIMAL(10,2)) / ItemsChecked) * 100, 6, 2) + '%' 
        AS PassRatePercent
FROM BatchResults;

Now the output looks like:

BatchID  PassRatePercent
-------  ---------------
1         95.00%        
2         88.00%        
3         82.00%        

This is often enough for a report, depending on whether you expect or want any decimal places to be included.

Step 4 (Optional): Cleaning Up the Extra Decimal Places

Sometimes you don’t want 95.00%; you just want 95%. You can control that with ROUND() or by adjusting the cast.

SELECT 
    BatchID,
    STR((CAST(ItemsPassed AS DECIMAL(10,2)) / ItemsChecked) * 100, 6, 0) + '%' 
        AS PassRatePercent
FROM BatchResults;

Now the results are:

BatchID  PassRatePercent
-------  ---------------
1            95%        
2            88%        
3            82%        

This looks cleaner for most reports.

When You’re Using Reporting Tools

If you’re pulling this data into a reporting tool like SQL Server Reporting Services (SSRS), Power BI, or even Excel, you might not need to do any manual formatting it like we did here. Many of these tools can format decimals as percentages automatically. In those cases, it’s often best to leave the column as a plain decimal (like 0.95) and just let the reporting layer handle the formatting.

But if your report is straight out of SQL Server (like a query that users export directly), then formatting with the % sign in SQL itself will probably be what you need to do.

Summary

• SQL Server doesn’t store percentages natively; it uses decimals.
• Multiply by 100 to turn a fraction into a percentage.
• Cast to VARCHAR (or use STR()) and add % if you need a human-friendly display.
• Use ROUND() to avoid unwanted decimal places.
• If you’re handing the data off to another tool, sometimes it’s better to leave it as a decimal and format it there.

Formatting percentages in SQL isn’t complicated once you know the basics. Start with clean decimal calculations, then decide whether SQL itself or your reporting tool should handle the final formatting.

Other Options for Formatting as a Percentage

There are various ways to achieve the same percentage formatting in SQL Server. For example, we can take advantage of the FORMAT() function with its P format string that is used specifically for percentage formatting. Here are 4 Ways to Convert a Number to a Percentage in SQL Server.