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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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

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

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 trialYes, 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

Your Business requirement or logic should tell that ( as we can't decide that).

Kindly check your requirement to confirm that.

Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

Details:

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