Complex arithmetic calculations in SQL

Hi everyone,

I have a situation where I have to get the result in SQL of somehow complex calculation which we were previously doing it in Ms Excel. Attached is the file to see the table structure and data. Now by keeping in mind the structure of the table, kindly help how to solve the following formula for "UK" where "LocationID" is different but "LocationSubID" are same.

Average = (((Itemsold_month-Itemsold_day)*Itemsold_total)+((Itemsold_month-Itemsold_day)*Itemsold_total))/(Itemsold_month-Itemsold_day)

This formula will be used to calculate for both entries of "UK" but their will be a single value, say "Average"

Please guide. If need any further clarification then please let me know.

P.S. This formula can be simplified but need to calculate the result using above mentioned formula only

Thanks.
Sample-table.xlsx
hennanra3Asked:
Who is Participating?
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.

Randy PetersonCommented:
So what happens when you have 3 different locations in the same country?  What you are trying to do is to get the average ((Itemsold_month-Itemsold_day)*Itemsold_total) for each country correct?  

Your formula is getting (from your table) Average = (((Itemsold_month-Itemsold_day)*Itemsold_total)+((Itemsold_month-Itemsold_day)*Itemsold_total))/(Itemsold_month-Itemsold_day)

But you are getting different values in your formula from different rows in your table?  Just trying to get the exact way you are trying to calculate.  SQL is very powerful doing aggregations and formulas like this, I just want to get it right.

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
Randy PetersonCommented:
Basically, if you could provide one concrete example from your table of your calculation, I should be able to generate the query.
hennanra3Author Commented:
Yes agree SQL is indeed very powerful in doing aggregations. Used "SUM" and their result in query to get the result.
Thanks.
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

From novice to tech pro — start learning today.