Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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*

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?

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;

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

```
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
```

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
```

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.

All Courses

From novice to tech pro — start learning today.

Open in new window