Need help in SUM from 3 different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help in SUM from 3 different tables

Author

Topic

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 03:08:51

Hi, really need your guys help urgently, currently i need to sum up 3 fields from 3 tables. See my below statement:

SELECT (
SELECT SUM [Table 1].[Remaining Quantity]
FROM [Table 1]
WHERE ([Table 1].[Item No_] = '1010') AND
([Table 1].[Location Code]=’JURONG’)
)
-
(
SELECT SUM( [Table 2].[Quantity])
FROM [Table 2]
WHERE ([Table 2].[No_] = '1010') AND
([Table 2].[Location Code]='JURONG')
)
+
(
SELECT SUM( [Table 3].[Quantity])
FROM [Table 3]
WHERE ([Table 3].[Item No_] = '1010') AND
([Table 3].[Transfer-to Code]='JURONG')
)

which give a result of a figure. i.e 25 to my main statement which i need to include this inside the New Statement in bold:

SELECT [Table1].[Location Code], [Table1].[Item No_], [Table1].[Remaining Quantity], [Table2].[Search Description], [Table2].[Posting Group], [Table1].[Unit of Measure Code], [New Statement]FROM [Table1] INNER JOIN [Table2] ON [Table1].[Item No_] = [Table2].No_
WHERE ([Table1].[Location Code] = 'JURONG') AND ([Table1].[Remaining Quantity] <> '0')
GROUP BY [Table1].[Item No_]

How do i go about doing it?

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 03:13:04

i tried to use inner join but the figure is not correct. See below:

SELECT SUM([Table 1].[Remaining Quantity]) - SUM( [Table 2].[Quantity]) + SUM( [Table 3].[Quantity]) AS TEST
FROM [Table 1]
INNER JOIN [Table 4] ON [Table 1].[Item No_] = [Table 4].[No_ ]

INNER JOIN
[Table 3] ON [Table 1].[Item No_] = [Table 3].[Item No_]
AND ([Table 3].[Transfer-to Code]= [Table 1].[Location Code])

INNER JOIN
[Table 2] ON [Table 1].[Item No_] = [Table 2].[No_]
And ([Table 2].[Location Code]= [Table 1].[Location Code])

WHERE ([Table 1].[Location Code] = 'JURONG’) AND ([Table 1].[Item No_] = '1010')
GROUP BY [Table 1].[Item No_], [Table 1].[Location Code], [Table 4].[Search Description],
[Table 4].[Gen_ Prod_ Posting Group], [Table 1].[Unit of Measure Code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 03:17:38

[code]SELECT [Table1].[Location Code],
[Table1].[Item No_],
[Table1].[Remaining Quantity],
[Table2].[Search Description],
[Table2].[Posting Group],
[Table1].[Unit of Measure Code],
COALESCE(q.Yak, 0) AS [New Statement]
FROM [Table1]
INNER JOIN [Table2] ON [Table2].[No_] = [Table1].[Item No_]
LEFT JOIN (
SELECT [Item No_],
SUM([Remaining Quantity]) AS Yak
FROM (
SELECT [Item No_],
[Remaining Quantity]
FROM [Table 1]
WHERE [Item No_] = '1010'
AND [Location Code] = 'JURONG'

UNION ALL

SELECT [No_],
- Quantity
FROM [Table 2]
WHERE [No_] = '1010')
AND [Location Code] = 'JURONG'

UNION ALL

SELECT [Item No_],
Quantity
FROM [Table 3]
WHERE [Item No_] = '1010'
AND [Transfer-to Code] = 'JURONG'
) AS d
GROUP BY [Item No_]
) AS q ON q.[Item No_] = [Table1].[Item No_]
WHERE [Table1].[Location Code] = 'JURONG'
AND [Table1].[Remaining Quantity] <> '0'
GROUP BY [Table1].[Item No_][/code]

E 12°55'05.25"
N 56°04'39.16"

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 03:22:26

thks, i try that and let you know the result... thanks

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 03:42:07

Hi, i hit the error, invalid column name 'Quantity','Location Code 'and 'posting Group' when i run it in my sql analyzer...

may i know what happen?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 03:45:23

Don't ask me! I just copied the code you posted above!

You are not very consistent with table naming.
PLEASE REVISE CODE AND CHANGE TABLE NAMES AND COLUMN NAMES SO THAT THEY WILL FIT YOUR ENVIRONMENT.

E 12°55'05.25"
N 56°04'39.16"

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 03:56:10

sorry about it.... will tidy up and get back to you.... thanks.

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 04:06:08

Previously i have the below statement:

SELECT [Table 1].[Location Code], [Table 1].[Item No_],
SUM([Table 1].[Remaining Quantity]) AS Stock, [Table 4].[Search Description], [Table 4].[Gen_ Prod_ Posting Group], [Table 1].[Unit of Measure Code]
FROM [Table 1] INNER JOIN [Table 4] ON [Table 1].[Item No_] = [Table 4].No_
WHERE ([Table 1].[Location Code] = ‘JURONG’) AND [Table 1].[Item No_] = '1010']
GROUP BY [Table 1].[Item No_], [Table 1].[Location Code], [Table 4].[Search Description], [Table 4].[Gen_ Prod_ Posting Group], [Table 1].[Unit of Measure Code]

But now i want to select another field which pull from 3 tables. Table 1, Table 2 and Table 3 and there is a formula.

Statement A - Statement B + Statement C

Statement A
SELECT SUM ([Table 1].[Remaining Quantity])
FROM [Table 1]
WHERE ([Table 1].[Item No_] = '1010') AND
([Table 1].[Location Code]='JURONG')

Statement B
SELECT SUM( [Table 2].[Quantity])
FROM [Table 2]
WHERE ([Table 2].[No_] = '1010') AND
([Table 3].[Transfer-to Code]='JURONG')

Statement C
SELECT SUM( [Table 3].[Quantity])
FROM [Table 3]
WHERE ([Table 3].[Item No_] = '1010') AND
([Table 3].[Transfer-to Code]='JURONG')

So how do i put this new field into my main statement? thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 04:16:35

You do exactly as I posted earlier.
Are you by chance NOT using Microsoft SQL Server? It seems to me yuo are using MySQL.

E 12°55'05.25"
N 56°04'39.16"

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 05:52:27

i running MS SQL... but i hit the same error when generating.

Can help me to pinpoint what is the problem? thanks.

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 10:04:02

Hi, any kind soul can help? need to implement this by tomorrow.. really urgent... thks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 10:11:35

If you can't figure out what is wrong even after all this help, now is the time to pay an consultant to help you.

E 12°55'05.25"
N 56°04'39.16"

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 10:20:09

SOrry, peso... i am amateur to SQL and need someone to help so that i can finish off my project...

can smeone enlighten me? thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 10:24:33

It is very easy!
Use the technique I posted earlier and fix your spelling errors.

E 12°55'05.25"
N 56°04'39.16"

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-25 : 10:49:56

ok then. i try it out and let u know...

btw, u short of one table in ur statement... thanks

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-26 : 02:36:31

Bro, still cannot work...

But where is the minus and plus of the statement?

I need to do this for one of the field that i indicated

Statement A - Statement B + Statement C

I cant find this in yr statement... thks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 02:39:36

quote:

Originally posted by Peso

SELECT		[Table1].[Location Code],
		[Table1].[Item No_],
		[Table1].[Remaining Quantity],
		[Table2].[Search Description],
		[Table2].[Posting Group],
		[Table1].[Unit of Measure Code],
		COALESCE(q.Yak, 0) AS [New Statement]
FROM		[Table1]
INNER JOIN	[Table2] ON [Table2].[No_] = [Table1].[Item No_]
LEFT JOIN	(
			SELECT		[Item No_],
					SUM([Remaining Quantity]) AS Yak
			FROM		(
						SELECT	[Item No_],
							[Remaining Quantity]
						FROM	[Table 1]
						WHERE	[Item No_] = '1010'
							AND [Location Code] = 'JURONG'

						UNION ALL

						SELECT	[No_],
							- Quantity -- Here is the minus sign you are looking for!
						FROM	[Table 2]
						WHERE	[No_] = '1010')
							AND [Location Code] = 'JURONG'

						UNION ALL

						SELECT	[Item No_],
							Quantity
						FROM	[Table 3]
						WHERE	[Item No_] = '1010'
							AND [Transfer-to Code] = 'JURONG'
					) AS d
			GROUP BY	[Item No_]
		) AS q ON q.[Item No_] = [Table1].[Item No_]
WHERE		[Table1].[Location Code] = 'JURONG'
		AND [Table1].[Remaining Quantity] <> '0'
GROUP BY	[Table1].[Item No_]

E 12°55'05.25"
N 56°04'39.16"

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-26 : 03:08:55

SELECT [Table 1].[Location Code],
[Table 1].[Item No_],
SUM([Table 1].[Remaining Quantity]) AS Quantity,
[Table 4].[Search Description],
[Table 4].[Gen_ Prod_ Posting Group],
[Table 1].[Unit of Measure Code],
COALESCE(q.Yak, 0) AS [New Statement]
FROM [Table 1]
INNER JOIN [Table 4] ON [Table 4].[No_] = [Table 1].[Item No_]
LEFT JOIN (
SELECT [Item No_],
SUM([Remaining Quantity])AS Yak
FROM (
SELECT [Item No_],
[Remaining Quantity]
FROM [Table 1]
WHERE [Item No_] = '1010'
AND [Location Code] = 'EUROPE'

UNION ALL

SELECT [No_],
- Quantity
FROM [Table 2]
WHERE [No_] = '1010'
AND [Location Code] = 'EUROPE'

UNION ALL

SELECT [Item No_],
Quantity
FROM [Table 3 Line]
WHERE [Item No_] = '1010'
AND [Transfer-to Code] = 'EUROPE'
) AS d
GROUP BY [Item No_]
) AS q ON q.[Item No_] = [Table 1].[Item No_]
WHERE [Table 1].[Location Code] = 'EUROPE'
AND [Table 1].[Item No_] ='1010'
GROUP BY [Table 1].[Item No_], [Table 1].[Location Code], [Table 4].[Search Description],
[Table 4].[Gen_ Prod_ Posting Group], [Table 1].[Unit of Measure Code],q.Yak,[Table 1].[Remaining Quantity]

The result give two rows: see below:
By right, it should only give me one row with quantity of 55 and then the New Statement should give a quantity of 55. As Statement A(55) - Statement B (0) + Statement C(0)

Location Code Item No_ Quantity Search Description Gen_Prod_ Posting Group Unit Of Measure Code New Statement
EUROPE 1010 .00000000000000000000 1010 Description HP EA 55.00000000000000000000
EUROPE 1010 55.00000000000000000000 1010 Description HP EA 55.00000000000000000000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 03:21:59

You should really need to learn basic SQL!

Change COALESCE(q.Yak, 0) AS [New Statement] to SUM(COALESCE(q.Yak, 0)) AS [New Statement]

Remove this part ,q.Yak,[Table 1].[Remaining Quantity] from the GROUP BY statement.

E 12°55'05.25"
N 56°04'39.16"

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-26 : 03:23:25

Hi, tried another item number but it always have the double entry and also under the New Statement column, the quantity is not correct. Is always the same as the quantity column.

For example, for 0001 item number, the quantity is 910 (correct), but the New Statement Column show 910 as well (which is not correct, the correct figure is 907). Thanks

Illegal_Operation
Starting Member

16 Posts

Posted - 2007-10-26 : 03:47:22

quote:
Originally posted by Peso

You should really need to learn basic SQL!

Change COALESCE(q.Yak, 0) AS [New Statement] to SUM(COALESCE(q.Yak, 0)) AS [New Statement]

Remove this part ,q.Yak,[Table 1].[Remaining Quantity] from the GROUP BY statement.

E 12°55'05.25"
N 56°04'39.16"

i remove the lines that u suggest, and it works and it left with only one row. But however the New Statement Column figure shows 573375 which is incorrect...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources