Returning a Percentage of 2 Summed Values

I'm trying to use T-SQL to return the relative percentage of two summed values when I divide the values into each other, but I always get 0. Here's an example of the type of calculation I'm trying:

SELECT((SUM(Parts.Scrap) / SUM(Parts.Produced))*100) AS Per_ScrapFROMParts

Both the Scrap and Produced columns are defined as int data types in the Parts table. My calculation behaves almost as if the division operation is doing integer math. What am I doing wrong?

SQL Server does perform integer math during this calculation. Here's why: SQL Server sometimes will perform an implicit data-type conversion when it compares values that are based on different data types. SQL Server performs the conversion according to the rules that SQL Server Books Online (BOL) defines in the topics "Data Type Conversion" and "Data Type Precedence." However, SQL Server doesn't perform a data-type conversion when the data types are the same.

Related: Calculating Percentage

In your example, both data types are int, so the result of the operation is also an int instead of a decimal, which would be required to show the percentage value you're trying to generate. For example, the following calculation returns 0:

SELECT 3/4 * 100GO

You actually want the answer to be 75. But SQL Server sees you dividing the integer 3 by the integer 4. How can you work around this quirk? Either of the following two T-SQL statements will return the answer you expect:

SELECT 3.0/4.0 * 100GO

or

SELECT cast(3 as decimal)/ cast(4 as decimal) * 100GO

These examples return the value you're looking for because you're explicitly casting the integer values as decimals. The first example forces the integer to become a decimal by referencing the integer 3 as the decimal number 3.0. The second example uses the Cast() function to do the same.

Learn more: Manipulate and Calculate