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?
 
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
 
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 & ArchitectCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
 
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 & ArchitectCommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.