Performing mathematical operations on 4 columns in 4 different tables in a database using SQL

Question

0.00/5 (No votes)

See more:

This is my first time using SQL and I'm having a hard time figuring out the syntax on how to perform this query. I have 5 tables in a database (z, a1, b1, a2, b2) and each table has a column named qty (except table z). I need to be able to select the following:

z.ID, z.Name, z.Name2, z.Cost, (a1.qty + b1.qty )-(a2.qty + b2.qty) as quantity

I can call z.ID, z.Name, z.Name2, z.Cost no problem, but I'm having difficulty with the rest of the expressions.

This is what I have and I am 100% positive its wrong:

<br />
select<br />
	z.ID,<br />
	z.Name,<br />
	z.Name2,<br />
	z.Cost,<br />
	(a1.qty + b1.qty) - (a2.qty + b2.qty) Quantity<br />
from<br />
	z inner join<br />
		a1 on z.ID = a1.z_ID inner join<br />
		a2 on z.ID = a2.z_ID inner join<br />
		b1 on z.ID = b1.z_ID inner join<br />
		b2 ON z.ID = b2.z_ID<br />

I've searched all over google, but I still can't figure it out. Any help on this will be extremely helpful! Thank you in advance.

Posted 27-Jun-11 23:08pm

Jester69

Updated 27-Jun-11 23:26pm

R. Giskard Reventlov

v2

Add a Solution

Comments

[no name] 28-Jun-11 5:17am

can you give me the table structure for all five tables. then only i'll be able to help you out.

Jester69 28-Jun-11 5:20am

table z
id
name
name 2
cost

Table a1
id
qty
z_id

Table a2
id
qty
z_id

Table b1
id
qty
z_id

Table b2
id
qty
z_id

its a very simple table struct ^^

Simon_Whale 28-Jun-11 6:01am

Can I ask why you think its wrong? as you are correctly joining 3 tables to the first table (z)

Jester69 28-Jun-11 21:05pm

Hi :)
Table z only has a round 500 rows. Logically I'm thinking, since I'm just adding a unique column it should spit out the same number of rows. However when I run this script it takes forever to finish and the output is more than 44k rows :(

2 solutions

Solution 1

See this to know how join works.
http://msdn.microsoft.com/en-us/library/aa213227(v=sql.80).aspx[^]

Use this in your case

SQL

SELECT z.ID, z.Name, z.Name2, z.Cost, (a1.qty + b1.qty )-(a2.qty + b2.qty) as Quantity
FROM z, a1, a2, b1, b2
WHERE
           z.ID = a1.z_ID
           AND z.ID = a2.z_ID
           AND z.ID = b1.z_ID
           AND z.ID = b2.z_ID

Posted 27-Jun-11 23:23pm

Prerak Patel

Comments

Jester69 28-Jun-11 22:15pm

Hi :)
I tried your suggestion yesterday, but I still kept coming up with the same results instead of the original 500 rows i got 44k+ rows. :( What i need is for the table z to spit out the same number of rows but an added column for quantity of the particular name.

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)

Jester69 · Accepted Answer · 2011-06-28T19:47:00

Hi i was able to figure this out after i did some research all night and into the early morning. i used subqueries and unions to pull this off. this is the code i made

SQL

SELECT Z_ID, Z.Name, Z.Name2, cast(Z.UnitPrice as numeric(13,2)) as UnitPrice, abs(SUM(IL)) AS Qty, cast(UnitPrice * abs(SUM(IL)) as numeric(13,2)) as TotalAmount
FROM
(
    SELECT A1.Z_ID, sum(A1.QTY) as IL
    FROM A1
    GROUP BY A1.Z_ID
    UNION
    SELECT B1.Z_ID, sum(B1.QTY) as PL
    FROM B1
    GROUP BY B1.Z_ID
    union
    SELECT B2.Z_ID, sum(B2.QTY) * -1 as SL
    FROM B2
    GROUP BY B2.Z_ID
    union
    SELECT A1.Z_ID, SUM(A1.QTY)as ISL
    FROM A1
    GROUP BY A1.Z_ID
) MergedTable, Z
WHERE MergedTable.Z_ID = Z.ID
GROUP BY Z_ID, Z.Name, Z.Name2, Z.UnitPrice

thank you everyone who helped! Thank you as well Prerak Patel for the useful link! I'll keep this in my toolbox for later use.