Sum column from inner join
How do you sum a column from an inner join?
I got this far but the results are not correct eg.
SELECT DISTINCT it.CODE, pl.UNITS
FROM ITEMDETAILS it inner join plant pl ON it.CODE = pl.CODE
WHERE it.LOCNUMBER = '3434';
It gives 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
Then I want to sum all UNITS in TOTAL, but when I execute the next query, does it get the wrong calculation? Can anyone show me the error of my paths?
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 should obviously be 9.5, I'm guessing it calculates the entire column and doesn't accept the where clause but not sure why?
Thanks for your help as always.
source to share
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
source to share
The purpose of merging a "detail table" into another is to facilitate the where "WHERE it.LOCNUMBER = '3434" clause. take it away and there is no purpose for joining.
It may be that this connection is the only reason for the unwanted repetition (aka "duplicates"), and therefore another method could be used. eg.
SELECT
pl.CODE
, pl.UNITS
FROM plant pl
WHERE pl.CODE IN (
SELECT
it.CODE
FROM ITEMDETAILS it
WHERE it.LOCNUMBER = '3434'
)
;
and
SELECT
SUM(pl.UNITS)
FROM plant pl
WHERE pl.CODE IN (
SELECT
it.CODE
FROM ITEMDETAILS it
WHERE it.LOCNUMBER = '3434'
)
;
Instead of IN (), you can use EXISTS () with correlation in a subquery.
Note that if dbms supports sum () over (), this can result in a grand total as a column.
source to share