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

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

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))

Group By Col4

gives a result different than

(SUM(COL1)*SUM(COL2)+SUM(C

Group By Col4

different than

SUM((COL1*Col2+Col3)/(Col1

and so on

Thanks

Details:

http://msdn.microsoft.com/en-us/library/ms187520(SQL.90).aspx

kindly check the exact order which you require and all three statements above are not one and the same.

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

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

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.

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

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