Adding Aggregates Doesn't Work

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)

Adding Aggregates Doesn't Work

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-13 : 19:49:58

SELECT sum([GroundShipments]), (sum(ExpressShipments)+sum(GroundShipments))
from SHIPRUSH_INFO.dbo.vwFirstAttemptMashup
where LastShipmentAtDT IS NOT NULL
group by Carrier, CM

Gives, say,

1,2; 5,7; 456,7678;

but

SELECT (sum([GroundShipments])/((sum(ExpressShipments)+sum(GroundShipments))))
from ebay.dbo.vwFirstAttemptMashup
where LastShipmentAtDT IS NOT NULL
group by Carrier, CM

calculates, but gives all zeroes!
Why? I've been having a lot of issues using two aggregates together. Are there special rules?

Thanks,
Arithmomaniac

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-13 : 19:54:02

integer divide by integer will give you back integer.
So 1 / 2 = 0
and 5 / 7 = 0

multiply it by 1.0 will force to convert the result of sum() to decimal value


SELECT  (1.0 * sum([GroundShipments])/((sum(ExpressShipments)+sum(GroundShipments))))
from ebay.dbo.vwFirstAttemptMashup
where LastShipmentAtDT IS NOT NULL
group by Carrier, CM

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-14 : 00:24:28

SELECT sum(1.0*[GroundShipments])/((sum(ExpressShipments)+sum(GroundShipments))))
from ebay.dbo.vwFirstAttemptMashup
where LastShipmentAtDT IS NOT NULL
group by Carrier, CM

Tan what happened to your colours?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-14 : 01:28:51

quote:
Tan what happened to your colours?

Just woke up when i posted that. Have not had my daily [spoiler]cuppa[/spoiler] yet

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-14 : 02:53:17

quote:
Originally posted by khtan

quote:
Tan what happened to your colours?

Just woke up when i posted that. Have not had my daily [spoiler]cuppa[/spoiler] yet

KH
[spoiler]Time is always against us[/spoiler]

Too much color

What does the hidden word mean?

Madhivanan

Failing to plan is Planning to fail

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-15 : 12:46:34

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources