Solved

Complex arithmetic calculations in SQL

Posted on 2016-08-24

1 solution

Medium Priority

141 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

Comment

Question by:hennanra3

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

3 Comments

LVL 8

Accepted Solution

by:Randy Peterson

Randy Peterson earned 2000 total points

ID: 417689512016-08-24

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.

LVL 8

Expert Comment

by:Randy Peterson

ID: 417689552016-08-24

Basically, if you could provide one concrete example from your table of your calculation, I should be able to generate the query.

Author Closing Comment

by:hennanra3

ID: 417698472016-08-25

Yes agree SQL is indeed very powerful in doing aggregations. Used "SUM" and their result in query to get the result.
Thanks.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to dynamically return a table structure using a Stored Procedure

Article by: Ryan Chong

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…

Export Table Data in SQL Server Management Studio

Article by: Dirk

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.

Executing a SQL Server Function from Within Access

Video by: TechMommy

Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

Dealing With Dates: Manipulating Dates (SQL Server)

Video by: Steve

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.