Solved

# Calculation in Query

Posted on 2009-04-08
345 Views
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
Question by:akalbfell
• 5
• 3

LVL 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 150 total points
ID: 24101384
>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

LVL 59

Assisted Solution

Kevin Cross earned 350 total points
ID: 24101393
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.

SELECT (B*2) + (C*3) + (D*4) AS total FROM DB;
0

LVL 59

Expert Comment

ID: 24101402
:) LOL -- Guess I agree with Angle Eyes.
0

LVL 8

Author Comment

ID: 24101456
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

LVL 59

Expert Comment

ID: 24101494
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
``````
0

LVL 59

Accepted Solution

Kevin Cross earned 350 total points
ID: 24101507
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
``````
0

LVL 8

Author Comment

ID: 24101549
Still produces 207 not 287
0

LVL 59

Expert Comment

ID: 24101603
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
``````
0

LVL 8

Author Closing Comment

ID: 31568234
ran it again just for sanity measures and it worked...nothing looks different...very strange...anyhow thanks guys, appreciate the quick responses
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …