asked
Combining columns using count
I have three tables like the following : Date Part_ID Inventory_count 1st jan 2015 123 1 31st jan 2015 123 2 table 2 : date Part_ID Manufactured 1st jan 2332 5 and a similar table 3. Now what i want is Part_id Inventory Manufactured Sold I.e for each part_id, count of how many were in the inventory,then how many got manufactured and how many got sold. The date is not required in the results.
What I am not seeing is a definition for table 3. How is that supposed to be used in the results?
"date is not required in the results" So, for the inventory, the part from 1st Jan 2015 should be counted as well as both parts from 31st Jan 2015? If a part is in the inventory on both dates, it will be double counted (or more, if it's in inventory for more than two cycles). To pull these all together, you just need to gather your counts grouped by part_id and join those results: ;with part_inventory as ( select part_id, sum(inventory_count) as inventory_count_sum from unnamed_table group by part_id ), part_manufactured as ( select part_id, sum(manufactured) as manufactured_sum from another_unnamed_table group by part_id) ,( same for sold...) select m.part_id, m.manufactured_sum, i.inventory_count_sum, ... from manufactured as m left join part_inventory as i on m.part_id = i.part_id left join... same for sold
22 People are following this question.
