Mastering SQL percentage calculations
in SQL Server

When working with databases, including SQL Server, it is nearly unavoidable to encounter situations where expertise in percentage calculations becomes essential due to the nature of the data. This skill may become crucial when it comes to sales data, financial reports, or any other database-driven applications. Here, we are going to provide you with a complete comprehensive guide on how to go about both the simplest and the most complex percentage calculations with the help of dbForge Studio for SQL Server, a tool specifically designed to enhance your database management experience:

• Visual query building: Simplify the process of constructing SQL queries, including percentage calculations, through an intuitive and user-friendly visual query builder.
• Data analysis tools: Utilize built-in data analysis tools that provide a clear overview of your dataset, making it easier to identify patterns and trends when working with percentages.
• Code snippet library: Access a library of SQL code snippets within the IDE, offering pre-built solutions for percentage calculations and reducing the need for manual coding.
• Error-fee query execution: Benefit from error-free query execution with the tool's advanced SQL code editor, helping you avoid common mistakes and ensuring accuracy in percentage calculations.
• Visual data representation: Leverage visualization features to represent percentage results graphically, enhancing your understanding of data and facilitating effective communication of insights.

Understanding SQL percentage basics

As we have stated before, mastering percentage calculations is a fundamental skill for an SQL Server database manager. Let us first get acquainted with the basics of the matter. Like any interaction with a computer, calculating percentages boils down to a mathematical operation, whether a straightforward or more intricate one. There is no built-in operator that calculates percentages in SQL Server, and you will have to rely on basic arithmetic operations like (number1/number2 x 100). Fortunately, SQL Server supports standard arithmetical operators that enable you to perform these calculations effortlessly without the need for mental gymnastics.

Calculating percentages between two columns

One of SQL's most straightforward approaches for percentage calculations involves the built-in aggregate functions. These functions are designed to perform calculations across a set of rows or individual rows, producing a singular result for the entire group. SUM, AVG, COUNT, and other aggregate functions are among the most frequently used for percentage calculations. These functions typically work with numeric values and return the results in decimal or integer format.

An alternative way to calculate percentages in SQL suggests using the division and multiplication operators. You can express the outcome as a percentage by dividing one value by another and subsequently multiplying the result by 100. This method finds application in different scenarios like calculating growth rates, market share, and profit margins.

Try it yourself with dbForge Studio

Even though SQL Server Management Studio (SSMS) is the most popular and familiar tool that allows you to work with SQL Server databases, it is not the only one. Moreover, in the continuously evolving world of database development, administration, and management, new GUIs keep appearing like mushrooms after the rain. How do you choose the tool that is perfect for you in this variety?

Let us compare dbForge Studio for SQL Server with SSMS so that you can make an informed decision on which solution best aligns with your daily requirements:

Take advantage of dbForge Studio for SQL Server by downloading a free fully-functional 30-day trial version and installing it on your computer. With a huge pack of advanced features and intuitive GUI, this all-in-one MSSQL tool can maximize productivity and make SQL Server database development, administration, and management process efficient. The Studio can also be of use when it comes to today's topic, from generating test data to performing advanced percentage calculations.

For a more visual comparison of the two solutions, watch the SSMS vs dbForge Studio for SQL Server - Features Comparison video on the Devart YouTube channel.

Data Generator for SQL Server

In order to demonstrate the percentage calculation process properly, we need a test database in the first place. Thus, we are going to create it and fill it with realistic data using Data Generator for SQL Server. You can use this tool as a part of dbForge Studio or download it as a separate solution. Moreover, Data Generator comes with a free add-in for SQL Server Management Studio that allows you to quickly populate your databases with meaningful test data right from the Management Studio Object Explorer.

Let us say we have created an empty BicycleStore database. The screenshot below graphically demonstrates the structure of the database, including tables, columns, connections, data types, foreign keys, etc.

Now, it is time to populate the database with test data:

1. In the Tools menu, click New Data Generation. The Data Generator wizard will open.

2. Specify the connection and select the BicycleStore database.

3. Click Next. The Options page will appear. Set the required options here.

4. Click Open. After processing, you will be presented with the data generation result.

You can specify the tables that you want to populate by selecting the check box that is located next to the table name. Further, you can define how you want the data to be generated: click the table name in the tree view and specify the details in the settings pane. All the changes are displayed in real time.

5. On the Data Generator toolbar, click .

6. The Data Population Wizard will open. On the Output page, you can select how to manage the data population script:

• Open the data population script in the internal editor.
• Save the script to a file.
• Execute the data population script against the database.

Select a required option and click Next.

7. On the Options page, configure the synchronization options. Click Next.

7. On the Additional Scripts page, type or select the script to be executed before and/or after the data population. Click Next.

8. The Summary page allows you to see the details of an error or warning. When you are setting up the tables and columns that you want to populate, dbForge Studio displays warning and error messages to inform you when there may be a problem with the data generation.

9. Click Generate to finish the process.

Practical exercises

By now, we have covered quite a load of theoretical information and prepared a database full of test data to finally move on to some practice. Before finding the SQL percentages across rows and columns, let us see how you can do that using two basic variables in SQL Server.

Finding percentages using two variables

1. Click New SQL on the Standard toolbar.

2. IN the SQL document that opens, you can type in the required script. The script below defines three float variables: @number1, @number2, and @percentage. Next, the @number2 variable is divided by @number1, multiplied by 100, and is stored in the @percentage variable. Finally, the SELECT statement is used to display the result.

DECLARE @number1 as FLOAT
DECLARE @number2 as FLOAT
 
DECLARE @percentage as FLOAT
SET @number1 = 150
SET @number2 = 50
 
SET @percentage = @number2/@number1 * 100
 
SELECT @percentage AS Result
                        

3. Click Execute on the SQL toolbar.

You will see the query results in the grid right below the SQL document.

Finding percentages between two columns

Next, let us apply this logic to an actual table and calculate the percentage between two columns: UnitPrice and TotalAmount.

SELECT
  OrderID
 ,UnitPrice
 ,TotalAmount
 ,UnitPrice / TotalAmount * 100 AS 'Percentage'
FROM OrderDetails;
                        

Advanced percentage calculations

Having warmed up doing simple tasks, let us move on to something more complex. Advanced calculations often involve scenarios where percentages must be computed across multiple rows. There are powerful techniques for achieving this in SQL, such as using subqueries and Common Table Expressions (CTEs). The OVER clause is another powerful tool that simplifies percentage calculations over a set of rows, mainly when working with window functions. This clause is beneficial in scenarios where you need to perform calculations across a specified range of rows.

Subqueries

In this example, the query calculates the percentage of each row's TotalAmount in the OrderDetails table relative to the total sum of TotalAmount across all rows in the same table. The result is a column showing the percentage contribution of each row's TotalAmount to the overall sum of TotalAmount in the entire table.

SELECT
  TotalAmount
 ,TotalAmount * 100 / SUM(TotalAmount) OVER () AS 'Percentage of Total'
FROM 
  OrderDetails;
                        

Common Table Expressions (CTEs)

1. This query creates a CTE named PercentageCTE based on the OrderDetails table, calculating the percentage for each row's QuantityOrdered relative to the corresponding TotalAmount. The final SELECT statement retrieves all columns from the CTE.

WITH PercentageCTE (QuantityOrdered, TotalAmount, Percentage)
AS
(SELECT
    QuantityOrdered
   ,TotalAmount
   ,(QuantityOrdered / TotalAmount) * 100 AS Percentage
  FROM OrderDetails)
SELECT
  *
FROM PercentageCTE;
                        

2. Unlike the previous example, this query uses ProductCTE to calculate the count of each unique ProductID in the OrderDetails table. Then, it retrieves the ProductID and calculates the percentage of each product's occurrence relative to the total sum of occurrences in the entire table. The result is presented in the Percentage Supplies column. This is achieved by dividing the count of each product by the sum of all product counts and multiplying it by 100. The use of the SUM(Product_Count) OVER () window function ensures the calculation considers the total count across all rows in the ProductCTE.

WITH ProductCTE(ProductID, Product_Count)
AS
(
    SELECT ProductID, COUNT(*) AS Product_Count
    FROM OrderDetails
    GROUP BY ProductID
)
SELECT 
    ProductID, 
    Product_Count * 100.0 / SUM(Product_Count) OVER () AS 'Percentage Supplies'
FROM 
    ProductCTE;
                        

Further learning

After everything has been said and done, we would like to provide you with several additional sources of information that can further deepen your SQL Server expertise:

• SQL Server Tutorials
• dbForge Studio Documentation
• dbForge Studio Video Tutorials
• Devart Academy