Rows show numbers... but SUM(Division) is 0?

[tqeonline]

So I have this

SUM([Passed Scripts]/[Total Scripts]) AS 'Actual %',
SUM([Projected Passed Scripts]/[Total Scripts] AS 'Projected %',

each sum displays 0 however...

Passed Scripts = 21
Project Passed Scripts = 7
Total Scripts = 33

so the sums should come back as

Actual% = 36%
Projected% = 21%

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[sheila11]

If the [Passed Scripts] is of Int type, I think SQL uses that type for the result. So if your result is a fraction, it gets a value of zero.

 

[gmmastros]

sheila11 is absolutely correct. This is (almost certainly) an integer math problem. The simplest way to prevent this problem is to force it to use decimal/floating point math. Like this:

SUM([!]1.0 * [/!][Passed Scripts]/[Total Scripts]) AS 'Actual %',
SUM([!]1.0 * [/!][Projected Passed Scripts]/[Total Scripts] AS 'Projected %',

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I just noticed that you want a percentage. As such, you should multiply by 100.0 instead of 1.0.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

that is much simpler than this

 cast(cast(sum([Passed Scripts])as decimal)/cast(sum([Total Scripts]) as decimal)*100 as int) AS 'Actual %', 
                cast(cast(sum([Projected Passed Scripts]) as decimal)/cast(sum([Total Scripts]) as decimal) *100 as int) AS 'Projected %',

your code works but i don't need a decimal so i did this:

                sum(100 * [Passed Scripts]/[Total Scripts]) AS 'Actual %', 
                sum(100 * [Projected Passed Scripts]/[Total Scripts]) AS 'Projected %',

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

Unfortunately, *not* using the "trick" will cause your values to be *slightly* wrong.

Let's use some of the numbers you presented. specifically the 21 and 33.

21 / 33 = 0.636363636363636363

As a percentage: 63.6363636363636
Rounded to a whole number: 64

I would recommend this:

[!]Convert(Decimal(3,0), [/!]sum(100 * [Passed Scripts]/[Total Scripts])[!])[/!] AS 'Actual %'

If you convert to an int, the fractional portion is truncated. Converting to a Decimal(3,0) causes rounding to the nearest whole number.

Ex:

Select Convert([!]int[/!],          100.0 * 21 / 33)
Select Convert([!]Decimal(3,0)[/!], 100.0 * 21 / 33)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom