How to SUM values in column where values in another column match.

[Andy Sears]

I am trying to SUM values in a column where values in another column are the same.

Here is what I need, but I know the 5th line won't work:

UPDATE manufacturing_detail
SET item_lead_time_1 = 
(SELECT SUM(cycle_time)
FROM routing_workcenter
WHERE routing_workcenter.routing = routing_workcenter.routing)<---HELP!
FROM manufacturing_detail, routing_workcenter
WHERE manufacturing_detail.item = routing_workcenter.routing

[Sandeep M]

Try this

UPDATE manufacturing_detail 
SET item_lead_time_1 =  
(SELECT SUM(cycle_time) 
FROM routing_workcenter 
WHERE routing=manufacturing_detail.item)

[Andy Sears]

The problem I am having is comparing the same column in a single table, just for different rows. Not a column in one table to a column in another table. There will be multiple records with the same value in the routing column, when this occurs, I want to sum the values in the cycle_time column for those matching records.

[ck9663]

Could you post some sample data and the result you want the query to return?


~~ CK

[Andy Sears]

Here is a sample table:
routing_workcenter
routing_id routing status cycle_time
1 A A 10
2 A A 12
3 A A 15
4 B A 10
5 B A 10
6 B A 10
7 C A 15
8 C A 10
9 C A 25

What I want that 5th line of code to do is SUM based on the same value in the routing column. The results I am looking for would be:
A - 37
B - 35
C - 50

I then want to update item_lead_time_1 from table manufacturing_detail with these summed values.

[ck9663]

Where did you get the 35 for B? Or it should be 30 instead?

~~ CK

[Andy Sears]

Yes, it should be 30. Sorry.

[ck9663]

Here, read this...

Happy Coding!!!

~~ CK