473,563 Members | 2,326 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 4997
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
ck9663
2,878 Recognized Expert Specialist
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_workcen ter
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_d etail with these summed values.
Nov 3 '10 #5
ck9663
2,878 Recognized Expert Specialist
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
ck9663
2,878 Recognized Expert Specialist
Here, read this...

Happy Coding!!!

~~ CK
Nov 4 '10 #8

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

Similar topics

5
10759
by: Aaron C | last post by:
Hi, I'm trying to do an insert with the following statement: INSERT INTO user VALUES ( 'ag@ag.com','ag','Aaron','Chandler','','','La Mirada','CA',90638,714,'',''); and I'm getting the error message "Column count doesn't match value count at row 1".
3
2201
by: uw_badgers | last post by:
Is it possible to create a unique constraint to a column from another table? For example: tb_current: current_names -------------- aaa bbb tb_new:
3
3147
by: ssb | last post by:
Hello, This may be very elementary, but, need help because I am new to access programming. (1) Say, I have a column EMPLOYEE_NAME. How do I fetch (maybe, cursor ?) the values one by one and populate a combo box with these names. (this way, I can display all the EMPLOYEE_NAME values) (2) In general, can I do additional processing on...
1
14285
by: Uli | last post by:
Hi, A collegue of mine has made an access entry field and keyed in all the data. Now I have to key in the same data in the same database. However, I first need to delete the values in several colums in order to start. i.e. i need the current values of a certain column to replace with the default value. I have one column with 1,2,3 values,...
4
6440
by: Ying Lu | last post by:
Hello, Under mysql, we have "desc tablename" to get the detail information about a table. My question is about to get column name, and column type for a specific table under PostgreSQL through JDBC. Assume we have a table named "test". Under PSQL, we can input "\d test" to see the details about table "test" successufully. However, my...
0
4087
by: tania | last post by:
i have this table in my database: CREATE TABLE FILM( F_ID INT(5) NOT NULL AUTO_INCREMENT, F_TITLE VARCHAR(40) NOT NULL, DIRECTOR_FNAME VARCHAR(20) NOT NULL, DIRECTOR_LNAME VARCHAR(20) NOT NULL, TYPE VARCHAR(30) NOT NULL, DURATION TIME , YEAR_RELEASE YEAR NOT NULL, DESCRIPTION TEXT,
3
11030
by: sql guy123 | last post by:
This is a real challenge. I hope someone is smart enough to know how to do this. I have a table TABLE1
4
2190
by: drago | last post by:
Hi guys, I will be quick this time... Just want to know how we can calculate generic values in the same column. I am using invoice form with a services subform . I have a column of 'total' in subform and I want to add all values of that column to get the total cost on main invoice form. The 'total' column contains service cost of different...
0
1707
VbaNewbee
by: VbaNewbee | last post by:
I have a form with a few filters. Once the user clicks "search button", the code first evaluates my filters, then shows the query results in a List Box" titled backschedule. I have a few text boxes below the list box that add sum values in the query columns using Nz() Function. Example "openpoqtytotal = openpoqtytotal +...
0
1739
by: sk27ahmed | last post by:
Hi Any one can show me how to access datagridview column value on column checked unchecked. I create one column in datagridview of type checkbox,and on button click i write code to select all rows in the datagridview. The code for select all is for (int i = 0; i < dataGridView1.Rows.Count; i++) { ...
0
7658
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7579
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6235
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5479
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3612
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2077
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
909
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.