How to get this SUM(qty) - SQL Server Forums

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

SQL Server Development (2000)

How to get this SUM(qty)

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-11-02 : 08:40:31

Hi, i need once more a help

I have to tables TableIN, TableOut

TABLEin (KEY = CODE, LOTE)
in_code in_Lote in_qty
08 ABC 587
08 XRT 400

40 ABC 500
40 RUK 300

TABLEout (key = code, lote, ped)
out_code out_Lote out_qty out_ped
08 ABC 100 123
08 ABC 187 444
08 XRT 100 333
08 XRT 050 101

40 ABC 500

I would like a Select giving this result

code Lote QTin QTout IN - Out
08 ABC 587 287 300
08 XRT 400 150 250
40 ABC 500 500 0
40 RUK 300 0 300

any help will be apreciated

Tks in advance
C.Lages

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-02 : 08:54:22

You need the following concepts/constructs
a) LEFT JOIN ... for when you have no data in the "out" file.
b) CORRELATED subquery .... to work with records associated with some parent in the "IN" file
c) GROUP BY to group records to enable part (d)
d) SUM .... for totalling the subrecords
e) ALIAS .... to avoid having to repeat a calculation for the IN-OUT portion

Search here for example of each and build up the code slowly to understand how each works.
Have a go and revert with your best effort.

CLages
Posting Yak Master

116 Posts

Posted - 2005-11-02 : 09:11:07

This is my Problems
when i do

select in_code, in_lote , sum(in_qty) , sum(out_qty)
from TableIn
Left Outer join Tableout on
In_code = Out_code
group by in_code, in_lote

in_qty is sum twice for code 08,
in other words if i have in TableOUT code 08 two times
my select fails.

I did not found yet a reason, or maybe i am in the wrong way
tks
Clages

CLages
Posting Yak Master

116 Posts

Posted - 2005-11-02 : 09:40:38

Andrew
tks, i found by myself my mistake
by the way the answwer is:

select
lote_numlote ,
(select sum(lote_quant) from lotee as Le1 where le1.lote_codigo = Le.lote_codigo and le1.lote_numlote = Le.lote_numlote ) ,
(select sum(lots_quant) from lotes as Ls1 where ls1.lots_codigo = Le.lote_codigo and ls1.lots_numlote = Le.lote_numlote)
from lotee as LE
where Le.lote_codigo = '08'

C. Lages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources