Solved

# SQL sum qty where childs are duplicated

Posted on 2009-05-01
988 Views
I have 2 tables oitt and itt1.  OITT is the parent table and ITT1 is the child table.  I want to query the father, child, quantity of child.  But in the quantity result i want to sum where childs are the same.

This is a Bill of Material table.  My objective is to list all like child components and sum their quantities so I can order parts at one time.

I wrote this query but it is not summing the duplicate childs (its ok that they are duplicates because the father is different).

Select t1.father as 'Parent', t1.code as 'Child', t2.itemname as 'Description', sum (t1.quantity)
FROM ITT1  T1 INNER JOIN OITM T2 ON T1.Code = T2.ItemCode
WHERE t1.father in ('117','114')

I know I'm missing the group by but it's still not returning the expected results.  Maybe I can't sum duplicate rows because t1.father is in the selection?
0
Question by:strvenmrvn

LVL 57

Expert Comment

Try this one out:
Select t1.father as 'Parent', t1.code as 'Child', t2.itemname as 'Description', sum (t1.quantity)

FROM ITT1  T1 INNER JOIN OITM T2 ON T1.Code = T2.ItemCode

WHERE t1.father in ('117','114')

group by t1.father, t1.code, t2.itemname

0

Author Comment

Childs still appear.  See the attached image.
bom-query.JPG
0

Accepted Solution

by removing the t1.father from select and from group by the results are good.  I then want to be able to show who the t1.fathers are for each item.

so t1.father would appear as 117,114 in a returned cell.
bom-query2.JPG
0

LVL 40

Expert Comment

Can you provide some sample data from your two tables and expected result?
0

LVL 12

Expert Comment

If I understand your request (based on your last added comment), it sounds like you want a string representation of the set of fathers for each child (which could look like a comma-separated list, for example).  If you knew you had at most two fathers for each child, you could self (outer) join ITT1 to itself, and then build the "Fathers" field by CONCATing the father from ITT1 A to the father from ITT1 B.  However, this solution does not hold up if there could be more than 2 fathers for a child, and extending it out to N would be more trouble than its worth.

For a general solution, I believe you need a custom function for generating that string representation, using the 'code' from ITT1 as the input.  The actual function (or procedure) would greatly depend on the underlying database.  The code snippet below is just an example of how I'd attempt such a function with PostgreSQL.  (Oracle would likely be similar.)  I followed it with an example SQL query that would use the function.

Note that the use of the function does mean that there is an additional query for each and every child record returned.  Thus, this will be a performance hit on the returned query.
CREATE FUNCTION mk_parent_str(itt1_code VARCHAR) RETURNS VARCHAR

AS \$\$

DECLARE

str VARCHAR := '';

BEGIN

FOR row in (SELECT parent FROM ITT1 WHERE code = itt1_code)

LOOP

str := str || ', ' || row.parent;

END

RETURN str;

END;

\$\$ LANGUAGE plpgsql;

Select t1.code as 'Child', t2.itemname as 'Description', mk_parent_str(t1.code) as 'Parents', sum (t1.quantity)

FROM ITT1  T1 INNER JOIN OITM T2 ON T1.Code = T2.ItemCode

//WHERE t1.father in ('117','114')

group by t1.code, t2.itemname;

0