Aggregate field math 1

[hrhadin]

I have a written a query which asks for the results of a couple of aggregate calculations:

count(*) as nTotal,
SUM(nTVC) as nVTotal

Results nTotal = 4754
nvTotal = 11911

However, when I add this line to the query:

count(*)/SUM(nTVC) AS nVPercent

Results nTotal = 4754
nVTotal = 11911
nVPercent = 0

What am I doing wrong?

 

[gmmastros]

This is a case of 'integer' math.

For example:

[tt][blue]
Select 1/2

Results in 0
[/blue][/tt]

However, if you convert the integer to numeric/float, then the results would be as expected. Simply multiplying the number by 1.0 would accomplish this.

Ex:

[tt][blue]
Select 1.0 * 1/2
[/blue][/tt]

So....

[!]1.0 * count(*)/SUM(nTVC) AS nVPercent[/!]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I think you need to try using CAST or CONVERT function to get your two columns formatted as decimal. LIke so:

cast(count(*) as decimal)/cast(SUM(nTVC) as decimal) AS nVPercent

If you run this query I think you will see what your problem is:

select cast(3 as decimal)/cast(10 as decimal)

select 3/10

Hope it helps,

Alex




Ignorance of certain subjects is a great part of wisdom

 

[hrhadin]

Thanks to all who replied. Your tips were valuable in resovling my problem