Sum Problem 1

[ntech]

I am trying to sum up a value in a field and it is driving me nuts!

Common Table1 Table2
Sku qty Qty2
123345 3 4
1234 2 7
123345 3 4
123 4

Result should be

123345 6 8
1234 2 7
123 4


Anyone help me on what should be a simple question ?

Thanks

 

[MakeItSo]

In order to be on the safe side, you should create 3 queries:
query1 contains Common.SKU & SUM(Table1.qty), Grouped.
query2 contains Common.SKU & SUM(Table2.Qty2), Grouped.
query 3 contains Common.SKU, Sum(qty) from query1 and Sum(Qty2) from query 2, both queries joined on SKU

Sounds messy, but should do fine.
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[ntech]

Thanks for the reply.

The first 2 steps work ok but in joing the 2 querys some records do not appear if they are not in both sku fields.

Any ideas for an easy solution?

 

[MakeItSo]

You could include the Common table in query3, select common.sku and create left joins from your queries to the common table on SKU (display all from query and only those from table matching)

 

[lynchg]

Try this:

qrySku:
Select Distinct SKU From Table1
Union
Select Distinct SKU From Table2

Select qrySku.SKU, Sum(Table1.qty), Sum(Table2.qty2)
From qrySku (Left Join Table1 On qrySku.SKU = Table1.SKU) (Left Join Table2 On qrySku.SKU = Table2.SKU)
Group By qrySku.SKU

The syntax of the join statements may not be perfect but you should get what you want. qrySku gives you a list of all of the SKU values from both tables and the outer joins allow you to retrieve all of the SKU values even when that value does not appear in one or the other table.

HTH

 

[Golom]

Try

   Select DISTINCT A.SKU,

          (Select SUM(X.Qty) From Table1 X
           Where  X.SKU = A.SKU) As SumQty,

          (Select SUM(X.Qty2) From Table2 X
           Where  X.SKU = A.SKU) As SumQty2

   From Common As A

 

[ntech]

Thabks for all you replys.