sum in two different tables

[KryptoS]

Hello,

I'm searching for a query where I can get the sum out off 2 different tables with a join. It's about barcoding and keeping my stock. So you have barcodes in, and barcodes out.

barcodes_in:
barcodeinID
code
amount

barcodes_out:
barcodeoutID
barcodeinID
amount

barcodeinID code amount
1 1 5
2 1 5
3 2 10
4 2 15

barcodeoutID barcodeinID amount
1 1 3
2 1 2
3 2 2
4 4 10

I want per code the amount in, and out. So in my example this will give

code in out
1 10 7
2 25 10

is this possible?

The One And Only KryptoS

 

[r937]

could you explain how you got those out numbers?

seems to me they should be

code in out
1 10 5
2 25 2


r937.com | rudy.ca

 

[KryptoS]

hmm my columns moved a little..


for in:
code 1 = barcodeinID 1 and 2 = 5 + 5 = 10
code 2 = barcodeinID 3 and 4 = 10 + 15 = 25

for out:
code 1 = barcodeoutID 1 and 2 and 3 = 3 + 2 + 2 = 7
code 2 = barcodeoutID 4 = 10

hopes this helps?

The One And Only KryptoS

 

[r937]

select code
     , sum(in) as in
     , sum(out) as out
  from (
       select barcodes_in.code
            , sum(barcodes_in.amount) as in
            , 0                       as out
         from barcodes_in
       group
           by barcodes_in.code
       union all
       select barcodes_in.code
            , 0    
            , sum(barcodes_out.amount)
         from barcodes_in
       inner
         join barcodes_out
           on barcodes_out.barcodeinID = barcodes_in.barcodeinID
       ) as u
group
    by code

r937.com | rudy.ca

 

[KryptoS]

wooooooooow is that all

ok I managed to do it with 2 seperated queries. But thanks for this. I'm going to analyze it when I have a lot of free time .

The One And Only KryptoS