Store Sum in Variable Sql Then Use It Again

In this article, you will see the different means to summate SQL pct between multiple columns and rows. You will also see how to calculate SQL percentages for numeric columns, grouped by categorical columns. You volition utilise subqueries, the OVER clause, and the common table expressions (CTE) to find SQL percentages.

And so, allow'due south begin without any ado.

Finding Pct using Ii Variables

In that location is no congenital-in operator that calculates percentages in SQL Server. You accept to rely on bones arithmetic operations i.e. (number1/number2 x 100) to observe percentages in SQL Server.

Before finding the SQL percentages across rows and columns, let's get-go run into how y'all tin can find percentages using ii basic variables in SQL Server.

The script below defines iii bladder variables @num1, @num2 and @perc. Next, the @num2 variable is divided past the @num1 variable and the result is multiplied by 100 which is stored in the @perc variable and is printed on the console.

DECLARE @ num1 as Bladder DECLARE @ num2 equally Bladder DECLARE @ perc every bit Bladder SET @ num1 = 150 Set up @ num2 = l Ready @ perc = @ num2 / @ num1 * 100 Impress @ perc

Output:

Finding Percentages Between Two Columns

Finding Percentages between two columns is straightforward. You tin can simply use the cavalcade names and the partitioning operator "/" to dissever values in one column past some other. The consequence is a list of values that correspond to the result of the sectionalisation of all the values in the 2 columns.

Let's meet an example.

The script below, creates a tabular array Result with two bladder type columns "obtained", and "total". The script likewise inserts five dummy rows in the Consequence table. The SELECT query then selects all the records in the Result tabular array. Hither is an example:

CREATE TABLE Result ( obtained float , total bladder ) INSERT INTO Consequence values ( 15 , 50 ) , ( 10 , fifty ) , ( xx , 50 ) , ( 40 , l ) , ( 25 , fifty ) SELECT * FROM Result

Output:

Let'due south try to find percentages for each row as a event of the division between the values in the "obtained" and "total" columns every bit shown beneath where a new cavalcade is added for percentages.

SELECT obtained , total , obtained / total * 100 equally 'per centum' FROM Outcome

Finding Percentages via Subqueries

Finding SQL percentages between ii columns is straightforward. However, the procedure is not as straightforward for finding percentages beyond rows for different scenarios.

Permit's first talk over a very elementary scenario where you have to detect what is the percentage of a value in a cavalcade among all the rows in the column.

The post-obit script creates a tabular array Scores with one column.

CREATE Table Scores ( val float ) INSERT Scores ( val ) values ( fifteen ) , ( 10 ) , ( 20 ) , ( twoscore ) , ( 25 ) ; SELECT * FROM Scores

Output:

Now if you want to notice what percent of the sum of total values in the "val" cavalcade does each value constitutes, you lot can apply subqueries.

In this regard, the outer query will multiply all the values in the "val" column by 100 which volition be divided past the result of the subquery which finds the sum of all the values in the "val" column.

Let's first see how our subquery looks that calculate the sum of the values in the "val" column.

SELECT SUM ( val ) as 'Full Sum' FROM Scores

Output:

The following script returns the percentage of the total for each value in the "val " column.

SELECT val , val * 100 / ( SELECT SUM ( val ) FROM Scores ) as 'Percentage of Total' From Scores

If yous do not want to exclude any value while calculating the percentage of the full, you lot tin can do so with the WHERE clause as shown in the script below where the value 40 is not included.

SELECT val , val * 100 / ( SELECT SUM ( val ) FROM Scores WHERE val < 40 ) as 'Percentage of Full' From Scores WHERE val < 40

You can meet from the to a higher place output that the values now have a larger per centum share of total values since the value forty is removed.

Finally, as a side annotation, you tin can round off the percentages returned using the "round" office as shown beneath. The script below rounds off the percent values to ii decimal places.

SELECT val , circular ( val * 100 / ( SELECT SUM ( val ) FROM Scores WHERE val < 40 ) , 2 ) equally 'Pct of Total' From Scores WHERE val < xl

Output:

Let's at present see a real-earth example of how yous can calculate SQL per centum. You volition exist using the Northwind sample database which you lot can download and install from this link.

Run the following script to see the columns in the Products table.

Apply Northwind SELECT * FROM Products

Output:

The Products table contains columns that contain Supplier ID, Category ID, Unit of measurement Price and other data virtually the products in the Northwind database.

Consider a scenario where you lot have to find the percentage of the products supplied by each supplier. To detect such percentage values, you need 2 values:

1. The total number of all the products which you can get via the COUNT function
2. The full number of products supplied for each supplier, which you can get using the GROUP BY part.

You can then multiply the 2^nd value (count of products grouped by supplier ids) past 100 and then split the result by the 1^st value (full count of products).

Here is how you can use subqueries to find these such SQL percentages.

Employ Northwind SELECT SupplierID , count ( * ) * 100.0 / ( SELECT count ( * ) from Products ) as 'Supplier Percentage' FROM Products Grouping BY SupplierID

Output:

The in a higher place results evidence that the pct of products supplied by each supplier. For example, you can see that the supplier with id ane supplied iii.89% of the total products.

USING Over Clause

The Over clause is an extremely useful Window role that calculates values over a range of values. You can employ the Over clause to calculate SQL percentages likewise. With the Over clause, you can avert the utilize of subqueries for calculating percentages.

Let's see an example. The script beneath finds the percentage of products supplied by every supplier. Yous tin see that the following script is very similar to what you lot saw in the previous section. Even so, in this case, instead of using a subquery that returns the count of all products, we employ the Over clause which returns the sum of all the products.

Utilize Northwind SELECT SupplierID , count ( * ) * 100.0 / sum ( count ( * ) ) Over ( ) equally 'Supplier Percentage' FROM Products Group BY SupplierID

The output beneath is similar to what you lot accomplished using a subquery.

Output:

Let'south now run into a more circuitous example of finding SQL percentages. Consider a scenario where for each supplier you want to summate the percent of unit of measurement toll for all the products. In other words, y'all want to find out that what percentage of the sum of unit prices for all the products is being paid to a different supplier. You can practise so with the help of the OVER clause as follows.

In the script below, the supplier ids are displayed along with the sum of unit prices paid to all suppliers, and the percentages of unit of measurement prices paid to all suppliers.

To calculate the sum of unit of measurement prices, the formula is simple, you can utilise the SUM function and pass it to the column containing the unit prices.

To calculate the percentages for unit prices, you need to multiply the sum of unit of measurement prices for each supplier by 100 and then separate the result with the total sum of unit prices for all the suppliers. In the script below, in the denominator, the SUM office is called twice. Since we are using the OVER operator, the first SUM office only adds the prices for each supplier. To find the sum of unit prices paid to all suppliers, some other SUM office is called.

USE Northwind SELECT SupplierID , SUM ( UnitPrice ) as 'Total Price Product' , ( SUM ( UnitPrice ) * 100 ) / SUM ( SUM ( UnitPrice ) ) OVER ( ) equally 'Percentage of Total Cost' FROM Products GROUP BY SupplierID

In the output beneath, you lot can see the supplier ids, the sum of unit prices paid to each supplier and the percentage of unit prices for each supplier.

Output:

Using Common Tabular array Expressions

Finally, y'all can also utilize common table expressions (CTE) to calculate percentages. Let's start see how you lot tin can use CTEs to find percentages between values in 2 columns.

The script below finds the percentages by dividing the values in the "obtained" column by the values in the "full" column.

WITH ResultCTE ( Obtained , Total , Per centum ) AS ( SELECT obtained , total , ( obtained / full ) * 100 Pct FROM Result ) SELECT * FROM ResultCTE

Y'all tin can use CTE expressions to detect more than complex SQL percentages merely every bit yous did with the OVER clause. For case, the script below uses the CTE to calculate the percentage of products supplied past each supplier.

Utilize Northwind ; with ProductCTE ( SupplierID , Supplier_Count ) as ( select SupplierID , count ( * ) from Products group past SupplierID ) SELECT SupplierID , Supplier_Count * 100.0 / ( select sum ( Supplier_Count ) from ProductCTE ) equally 'Percentage Supplies' from ProductCTE ;

The consequence is similar to what you achieved via the OVER clause.

Output:

Finally, y'all can also use the OVER clause in combination with the CTE to summate percentages. For example, the script below uses the CTE to discover the percentage of products supplied by each supplier, along with the percentage of unit prices paid to all the suppliers for all the products.

Employ Northwind ; with ProductCTE ( SupplierID , Supplier_Count , Price_Percentage ) as ( select SupplierID , SUM ( UnitPrice ) every bit 'Total Cost Product' , ( SUM ( UnitPrice ) * 100 ) / SUM ( SUM ( UnitPrice ) ) OVER ( ) as 'Percent of Total Price' from Products group by SupplierID ) SELECT * from ProductCTE ;

Conclusion

In this commodity, we looked at different means to calculate SQL percentage betwixt multiple columns and rows.

• Writer
• Recent Posts

Ben Richardson runs Acuity Training a leading provider of SQL grooming the United kingdom. It offers a full range of SQL grooming from introductory courses through to advanced administration and information warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity's blog

View all posts by Ben Richardson

costillouner1968.blogspot.com

Source: https://www.sqlshack.com/sql-percentage-calculation-examples-in-sql-server/

Share this post