Inner Join two tables with two different SUMs

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 2000 Forums

Transact-SQL (2000)

Inner Join two tables with two different SUMs

Author

Topic

netagra
Starting Member

3 Posts

Posted - 2010-12-07 : 07:25:12

Hello to all,

it's nice to find you.

I have two tables one with production data the other is scrap data. The tables look pretty much identical, though i cannot make my query work.
Production table is like this
PrDate, PrUser, PrMachine, PrDept, PrItemCode, PrQuantity

Scrap table is like this
ScDate, ScUser, ScMachine, ScDept, ScItemCode, ScQuantity

What i need is to produce one table with a specific date that has both production and scrap quantities per user, item code and machine

What i have done is this

SELECT PrUser, PrMachine, PrItemCode, PrDept, SUM(PrQuantity), Sum(ScQuantity)
FROM Production LEFT JOIN Scrap ON PrUser = ScUser AND PrMachine = ScMachine AND PrItemCode = ScItemCode AND PrDept = ScDept
WHERE (PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00') AND PrDept = '611.4' AND Q9USER = '01225E'
GROUP BY PrUser, PrMachine, PrItemCode, PrDept
ORDER BY PrUser, PrMachine

But it doesn't work. It produces crazy amounts on sums! If i run two different queries each for the specific table i get my answers. The problem is i cannot join them in one table result.

What's wrong?

Thanks for your time!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 07:43:23

select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity, ScQuantity
from
(
select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity = SUM(PrQuantity)
from Production
where PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND PrDept = '611.4'
AND Q9USER = '01225E'
GROUP BY PrUser, PrMachine, PrItemCode, PrDept
) pr
left join
(
select ScUser, ScMachine, ScItemCode, ScDept, ScQuantity = SUM(ScQuantity)
from Scrap
where PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND PrDept = '611.4'
AND Q9USER = '01225E'
GROUP BY ScUser, ScMachine, ScItemCode, ScDept
) sc
on PrUser = ScUser AND PrMachine = ScMachine AND PrItemCode = ScItemCode AND PrDept = ScDept
ORDER BY PrUser, PrMachine

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

netagra
Starting Member

3 Posts

Posted - 2010-12-07 : 08:11:36

Thanks for your reply.

It doesn't like the
PrQuantity = SUM(PrQuantity)
ScQuantity = SUM(ScQuantity)
or something else?

This is what i get

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '='.

And sorry Q9User is PrUser !

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 08:31:54

Looks ok - is this sql server?
try
select PrUser, PrMachine, PrItemCode, PrDept, SUM(PrQuantity) as PrQuantity

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

netagra
Starting Member

3 Posts

Posted - 2010-12-07 : 09:40:07

Thanks again!

It worked perfectly.
I made some modifications and it looks like this

select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity, ScQuantity
from
(
select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity = SUM(PrQuantity)
from Production
where PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND PrDept = '611.4'
AND PrUser = '01225E'
GROUP BY PrUser, PrMachine, PrItemCode, PrDept
) pr
left join
(
select ScUser, ScMachine, ScItemCode, ScDept, ScQuantity = SUM(ScQuantity)
from Scrap
where ScDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND ScDept = '611.4'
AND ScUser = '01225E'
GROUP BY ScUser, ScMachine, ScItemCode, ScDept
) sc
on PrUser = ScUser AND PrMachine = ScMachine AND PrItemCode = ScItemCode AND PrDept = ScDept
ORDER BY PrUser, PrMachine, PrItem, PrStep

It combines these two queries in one and makes the results i need.
What is this technique called in order to work more with ?
Thank you so much.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 09:43:50

pr and sc are called derived tables.
You could also do the same thing with common table expressions which is sometomes more useful.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources