Sum/Ratio of other Sums - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum/Ratio of other Sums

Author

Topic

mwidjaja
Starting Member

8 Posts

Posted - 2010-06-07 : 12:52:40

Hi,

What's the best way to get a SUM/RATIO from multiple other SUMs.
e.g:
select top 100
SUM(x) AS X,
SUM(y) AS Y,
SUM(z) AS Z

I want to do this:
SUM(x)/(SUM(x)+SUM(y)+SUM(z)) AS RATIO_X,
(SUM(x)+SUM(y))/(SUM(x)+SUM(y)+SUM(z)) AS RATIO_XY,
(SUM(x)+SUM(y)-SUM(z))/(SUM(x)+SUM(y)+SUM(z)) AS RATIO_XYZ

How do I do another calculation like that?
Help is appreciated.

thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-07 : 14:25:27

We'd need to see the data structures of the tables you're pulling from and the query you're currently using and what the expected output is. You can do (sum x + y + z +..+n)/ sum( x + y +..+n), but that's essentially what you're doing now. There is a sum over() clause that may be beneficial, but we can't tell without more input from you.

Jim

Everyday I learn something that somebody else already knew

mwidjaja
Starting Member

8 Posts

Posted - 2010-06-07 : 18:57:19

I dont control the X,Y,Z data but they are simply columns with integers.
Something like this below, I just want to simply calculate the ratio/percentage of each X,Y,Z.
e.g: X/(X+Y+Z) or Y(X+Y+Z) or Z/(X+Y+Z)
The problem is, my result is always 0 which I think it does some rounding down.
I tried to use ROUND with 2 decimals, but still 0, anything I can do?

X | Y | Z
-----------
1 | 0 | 0
0 | 1 | 0
1 | 0 | 0
1 | 0 | 0
0 | 0 | 1
-----------+
3 | 1 | 1

Michael

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-07 : 19:12:39

Yes. Multiply the numerator by 1.0E. x*1.0E/(x=y+z). This is because in sql an integer divided by an integer is an integer, so 1/2 = 0 instead of .5.

Jim

Everyday I learn something that somebody else already knew

mwidjaja
Starting Member

8 Posts

Posted - 2010-06-08 : 00:31:00

Sorry, how do I overcome this?
How do I apply the numerator?

Michael

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-06-08 : 01:16:34

just multiply your numerator by 1.0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 02:36:52

Also refer this to know why you need 1.0
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail