Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Calculation in Query

I am trying to run a query against a MYSQL DB that is giving me the wrong calculation.
4 fields.. A is a total of B, C and D so that leaves me with the total and 3 of the 4 other values but i also need to calculate for the 4th value by subtracting values B,C, and D from the A..after that i need to multiply the values against a number and add it all together? Here is the formula that im using in case that makes no sense which i doubt it will...

(A-B-C-D) + (B*2) + (C*3) + (D*4) = Answer
Remember A is a total of B, C and D

not as complicated as my question i hope...any ideas the syntax to use?

here is the Query im trying which is giving the wrong result


SELECT ((A-B-C-D)+(B*2)+(C*3)+(D*4)) AS total FROM DB

0
akalbfell
Asked:
akalbfell
  • 5
  • 3
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Remember A is a total of B, C and D
does that not mean that:
A-B-C-D

should return 0 ?

please clarify with data samples what you get, and what you should get?
0
 
Kevin CrossChief Technology OfficerCommented:
Please give sample of data and expected results as your formula looks like it should do what you want assuming you want 2 x B, 3 x C and 4 x D to be the result.

Since A = B + C + D, then A - B - C - D = 0 has to be true.

Simply your formula is:
SELECT (B*2) + (C*3) + (D*4) AS total FROM DB;
0
 
Kevin CrossChief Technology OfficerCommented:
:) LOL -- Guess I agree with Angle Eyes.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
akalbfellAuthor Commented:
Here is an example of a row of data...

A= 180
B= 39
C= 4
D= 20

So the value i dont have which im calculating at the start is 180-39-4-20 which is 117
then i need to multiply B * 2(39*2), C * 3,(4*3) and D * 4(20*4)....Then add those 3 numbers to 117 which should give me my answer, 287,  but instead my query produces 207
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, ok.  This works fine for me.
SELECT (A - B - C - D) + (B * 2) + (C * 3) + (D * 4) AS Total
FROM (
	SELECT 180 AS A
	, 39 AS B
	, 4 AS C
	, 20 AS D
) t

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Try this and post a snippet of the results:
SELECT A, B, C, D, ((A-B-C-D)+(B*2)+(C*3)+(D*4)) AS Total
FROM DB

Open in new window

0
 
akalbfellAuthor Commented:
Still produces 207 not 287
0
 
Kevin CrossChief Technology OfficerCommented:
But post the line as it comes out from the query I posted.  It is hard to help when can't reproduce the problem and can't see what you are getting.

This simplier formula even worked for me as just reduce the number of B's, C's, and D's adding back by 1 and not subtract anything from A.

A, B, C, D, Total
180, 39, 4, 20, 287

Above is the result I get from my test query.
SELECT A, B, C, D, (A+B+(C*2)+(D*3)) AS Total
FROM DB

Open in new window

0
 
akalbfellAuthor Commented:
ran it again just for sanity measures and it worked...nothing looks different...very strange...anyhow thanks guys, appreciate the quick responses
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now