Aggregate MS SQL Can you explain this

Hello,
I am unable to grasp the concept of how aggregates works in MS SQL specifically the SUM aggregate
I googled for some answers but I am unable to find what I am lloking foir , I read about SUM DISTINCT and the fact that the way a join is made affects the final result, but I am still unable to see why aggregates behave the way they are.
Can you please give an explanation and a reference to a good article maybe(unless your answer is detailed ) explaining to me why
is
(SUM(col1*col2)+Sum(col3))/(SUM(Col1-Col2)-Sum(Col3))
Group By Col4
gives a result different than
(SUM(COL1)*SUM(COL2)+SUM(Col3))/((SUM(COL1)-SUM(Col2))-SUM(COL3))
Group By Col4
different than
SUM((COL1*Col2+Col3)/(Col1-Col2-Col3))

and so on

Thanks
LVL 9
xav056Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
The values will change based on perator precedence and use of parentheses.

Details:
http://msdn.microsoft.com/en-us/library/ms187520(SQL.90).aspx
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Order of columns involving Multiplication and Addition differs and Aggregation is performed on that which makes the difference.
kindly check the exact order which you require and all three statements above are not one and the same.
0
xav056Author Commented:
Still not getting the difference , I am aware of the precedence , but What I do not understand how exactly does the precedence work
I have a formula That I ma supposed to calculate, get the results and split that according to a range in another table B, based on values from A
for simplicity, assume we have 1000 rows each with col1,col2,col3,col4, and col5
Col5 determines the join on Table b
so it would be B  Left Join A where col5 between valueB1 and value B3
Group By BValue
Given the fact that B has about 20 rows only the 1000 rows from A should be split into those 20 Rows ,splitting determined by Col5 value
My formula in plain text is as follows
Col6 should equal
(
  (    (col1*col2)    +   (col3)     )   /   (   (Col1-Col2)   -   (Col3)     )
 )
SO the question would be when I group,  to get the value of Col6 I need to apply the above formula on each row, but what should I do when I group
SUM (
  (    (col1*col2)    +   (col3)     )   /   (   (Col1-Col2)   -   (Col3)     )
 )

OR
(
  (   SUM (col1*col2)    +   SUM(col3)     )   /   (  SUM (Col1-Col2)   -  SUM (Col3)     )
 )

OR
(
  (    SUM(col1)*SUM(col2)    +  SUM (col3)     )   /   (   SUM(Col1)-SUM(Col2)   -  SUM (Col3)     )
 )

Or something Else,
To me it looks more like option 2 above or maybe three, Can you please provide some information on how to determine that

Thanks

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

LowfatspreadCommented:
i can think of 2 immediate possibilities

1) are any of the columns nullable... if a column value is null then the addition etc operations will result in null at that time]
thus sum(a+b) isn't necessarily the same as sum(a)+sum(b)....

use sum(coalesce(a,0)+coalesce(b,0))  would give same result as sum(a)+sum(b)

2) do the datatype of the columns differ? you may lose precision with the arithmetic at different stages with the different sum's... you'd need to consistently explicity convert the columns to prevent it...


do either of these explain your scenario?


if not please give us more background info on the table(s) /column(s) involved and the
basic sql statement (joins. where clause etc ) you use.

some example data and the expected result would also assist
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xav056Author Commented:
1) are any of the columns nullable... if a column value is null then the addition etc operations will result in null at that time]
Yes, But I can't COALESCE as In some instances I will get division by zero exceptions.
The reason for the nulls is caused by the join, so in that instance the calculation should be null too.
2) do the datatype of the columns differ? you may lose precision with the arithmetic at different stages with the different sum's... you'd need to consistently explicity convert the columns to prevent it...
No the datatype is money for all the columns in the calculation

The major problem seems to be in wether I SUM befor multiplying or SUM each column then multiply.

Thanks
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> The major problem seems to be in wether I SUM befor multiplying or SUM each column then multiply.

Your Business requirement or logic should tell that ( as we can't decide that).
Kindly check your requirement to confirm that.
0
LowfatspreadCommented:
precisely my points...

do they apply...?

what are the datatypes of the columns involved...
0
xav056Author Commented:
according to my initial description above it look slike I have to multiple col1*col2 before summing. I guess its a simple math formula that I missed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.