Solved

Calculation in Query

Posted on 2009-04-08
9
341 Views
Last Modified: 2013-12-12
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
Comment
Question by:akalbfell
  • 5
  • 3
9 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
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

by:Kevin Cross
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.

Simply your formula is:
SELECT (B*2) + (C*3) + (D*4) AS total FROM DB;
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24101402
:) LOL -- Guess I agree with Angle Eyes.
0
 
LVL 8

Author Comment

by:akalbfell
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 59

Expert Comment

by:Kevin Cross
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

Open in new window

0
 
LVL 59

Accepted Solution

by:
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

Open in new window

0
 
LVL 8

Author Comment

by:akalbfell
ID: 24101549
Still produces 207 not 287
0
 
LVL 59

Expert Comment

by:Kevin Cross
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

Open in new window

0
 
LVL 8

Author Closing Comment

by:akalbfell
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now