472,127 Members | 1,482 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,127 software developers and data experts.

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

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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE manufacturing_detail
  2. SET item_lead_time_1 = 
  3. (SELECT SUM(cycle_time)
  4. FROM routing_workcenter
  5. WHERE routing_workcenter.routing = routing_workcenter.routing)<---HELP!
  6. FROM manufacturing_detail, routing_workcenter
  7. WHERE manufacturing_detail.item = routing_workcenter.routing
Nov 1 '10 #1
7 4872
Try this

Expand|Select|Wrap|Line Numbers
  1. UPDATE manufacturing_detail 
  2. SET item_lead_time_1 =  
  3. (SELECT SUM(cycle_time) 
  4. FROM routing_workcenter 
  5. WHERE routing=manufacturing_detail.item)
Nov 2 '10 #2
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.
Nov 2 '10 #3
2,878 Expert 2GB
Could you post some sample data and the result you want the query to return?

~~ CK
Nov 2 '10 #4
Here is a sample table:
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.
Nov 3 '10 #5
2,878 Expert 2GB
Where did you get the 35 for B? Or it should be 30 instead?

~~ CK
Nov 3 '10 #6
Yes, it should be 30. Sorry.
Nov 3 '10 #7
2,878 Expert 2GB
Here, read this...

Happy Coding!!!

~~ CK
Nov 4 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by sql guy123 | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.