Write a SQL Query to perform some calculation on fields

Question

1.00/5 (1 vote)

See more:

, +

I have a following table :

Billing_Acct   Multiplier   Total
111               12          200
111               12          400
123               5           100
123               5           300
123               5           600
241               10          400

Now I want another column whose results perform calculations like (for same Billing_acct I want to add Total and than multiply that by multiplier )

Result would be something like that:

Billing_Acct   Multiplier   Total     Result
111               12          200      7200
111               12          400      7200
123               5           100      5000
123               5           300      5000
123               5           600      5000
241               10          400      4000

How to write a Sql Query for this ?
or How to do this task in Excel ?

Thanks in advance.

Posted 14-Aug-15 2:23am

lovejeet0707

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-08-14T02:31:00

Solution 1

If I understood the requirement correctly, this can be done in several ways, for example something like:

SQL

SELECT a.Billing_Acct,
       a.Multiplier,
       a.Total,
       c.multiplier * c.total as result
FROM TableName a,
     (SELECT b.Billing_Acct,
            AVG(b.Multiplier) AS multiplier,
            SUM(b.Total)      AS Total
     FROM  TableName b
     GROUP BY b.Billing_Acct) c
WHERE a.Billing_Acct = c.Billing_Acct;

Posted 14-Aug-15 2:31am

Wendelius

v2

Comments

lovejeet0707 14-Aug-15 8:43am

I guess we don't need to take the avg of multiplier....I can directly multiply with the multiplier....!
Thanks for the Help...Worked like a Magic :)

Wendelius 14-Aug-15 8:53am

That's true, you can use the multiplier from the outer query. Glad if it helped :)