I do not get it

[dbware]

When I execute the following statement

SELECT DISTINCT OEL.item_no, OEL.qty_to_ship AS 'Total quantity Sold', OEL.sls_amt
FROM OEL
INNER JOIN OEH ON OEL.ord_no = OEH.ord_no
AND OEL.ord_type = OEH.ord_type
WHERE OEH.cus_no = '000000000027'
AND OEL.loc = 'SALES'
ORDER BY OEL.item_no

-----OUTPUT
07 .0000 .00
07 50.0000 69.38
07 50.0000 168.75

When I change the statement to sum the two field OEL.qty_to_ship and OEL.sls_amt.

SELECT DISTINCT OEL.item_no, SUM(OEL.qty_to_ship) AS 'Total quantity Sold', SUM(OEL.sls_amt)
FROM OEL
INNER JOIN OEH ON OEL.ord_no = OEH.ord_no
AND OEL.ord_type = OEH.ord_type
WHERE OEH.cus_no = '000000000027'
AND OEL.loc = 'SALES'
GROUP BY OEL.item_no
ORDER BY OEL.item_no

-----OUTPUT

07 200.0000 476.26

I believe that it should read

07 100.0000 238.13

The funny thing is that for some records it works just fine.

Thank you for your help

Mauro

 

[MeanGreen]

Try removing the DISTINCT on your first query and you will see that you have duplicate items with same amount of sums. The Group By will combine the duplicates giving you your value differences.

Good Luck!

 

[dbware]

When I remove the DISTINCT clause from the statement the output is as follow.

-----OUTPUT
07 200.0000 476.26

I believe that it should read

07 100.0000 238.13

Thank you,

Mauro

 

[MeanGreen]

Remove the DISTINCT from the FIRST 1st SQL statement where you do not do a group by. What do you get for output then?

SELECT OEL.item_no, OEL.qty_to_ship AS 'Total quantity Sold', OEL.sls_amt
FROM OEL
INNER JOIN OEH ON OEL.ord_no = OEH.ord_no
AND OEL.ord_type = OEH.ord_type
WHERE OEH.cus_no = '000000000027'
AND OEL.loc = 'SALES'
ORDER BY OEL.item_no

 

[dbware]

Thank you for you help MeanGreen.

I just realized that when something is wrong it always means that you overlooked something. In my case I realized after countless hours trying to find the problem, the problem was with the OEH table, it had two records with the same information.

Oh well, next time it will take half the time to find the problem!!!


Thank you again

Mauro