Solved

Complex arithmetic calculations in SQL

Posted on 2016-08-24
3
41 Views
Last Modified: 2016-08-25
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
0
Comment
Question by:hennanra3
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
Randy Peterson earned 500 total points
ID: 41768951
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.
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 41768955
Basically, if you could provide one concrete example from your table of your calculation, I should be able to generate the query.
0
 

Author Closing Comment

by:hennanra3
ID: 41769847
Yes agree SQL is indeed very powerful in doing aggregations. Used "SUM" and their result in query to get the result.
Thanks.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now