Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1007
  • Last Modified:

SQL sum qty where childs are duplicated

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
strvenmrvn
Asked:
strvenmrvn
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

Open in new window

0
 
strvenmrvnAuthor Commented:
Childs still appear.  See the attached image.  
bom-query.JPG
0
 
strvenmrvnAuthor Commented:
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
 
SharathData EngineerCommented:
Can you provide some sample data from your two tables and expected result?
0
 
cminearCommented:
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;

Open in new window

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now