Sum column from inner join

Question

How do you sum a column from an inner join?

I have got this far but the results are not correct, for example.

SELECT DISTINCT it.CODE, pl.UNITS
FROM ITEMDETAILS it inner join plant pl ON it.CODE = pl.CODE 
WHERE it.LOCNUMBER = '3434';

This give me this result which is correct

CODE    UNITS
GE-ARH  2
GE-ARV  2
GE-EC   0.5
GE-JB   0.5
GE-JT   0.5
GE-VL2  2
GE-VL4  2

I then want to sum all the UNITS into a TOTAL, but when I execute the below query it gives me the wrong calculation? Can anyone show me the error of my ways?

SELECT DISTINCT SUM(pl.UNITS) as TotalUnits 
FROM PLANT pl inner join ITEMDETAILS it on pl.CODE = it.CODE
WHERE it.LOCNUMBER = '3434';

TotalUnits
972

The answer obviously should be 9.5, I presume it is calculating against the whole column and not taking the where clause into consideration, but not sure why?

Thanks for your help as always.

Answer 1

You can do something like:

select sum(units) 
from
(
  SELECT DISTINCT it.CODE, pl.UNITS
  FROM ITEMDETAILS it inner join plant pl ON it.CODE = pl.CODE 
  WHERE it.LOCNUMBER = '3434'
) un

Or depending on the sql version

;with un as (
  SELECT DISTINCT it.CODE, pl.UNITS
  FROM ITEMDETAILS it inner join plant pl ON it.CODE = pl.CODE 
  WHERE it.LOCNUMBER = '3434'
)
select sum(units)
from un

Answer 2

I would do it like this

SELECT SUM(UNITS) AS TOTAL_UNITS
FROM
(
    SELECT DISTINCT it.CODE, pl.UNITS
    FROM ITEMDETAILS it inner join plant pl ON it.CODE = pl.CODE 
    WHERE it.LOCNUMBER = '3434'
) X