472,779 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Sum one Field base on matching of multiple column


I have a table with below 6 columns, I need to sum the amount together if Name + Campany + age + Gender are the same, and the result needs to keep at TotalAmount Column.

Name Company Age Gender Amount TotalAmount
Nick A 40 M 800
Nick A 40 M 1200
Nick B 40 M 400
Nick B 44 M 500
Belle A 40 F 600
Belle A 35 F 900
Moon C 59 F 500
Moon C 59 F 600

My desire result are below

Name Company Age Gender Amount TotalAmount
Nick A 40 M 800 2000
Nick A 40 M 1200 2000
Nick B 40 M 400 400
Nick B 44 M 500 500
Belle A 40 F 600 600
Belle A 35 F 900 900
Moon C 59 F 500 1100
Moon C 59 F 600 1100

I try below statement
update ProcessTable set TotalAmount = (select sum(Amount) from ProcessTable Group by Name, Company, Age, Gender)

But it give me error
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Can you teach me how to solve the issue?
Mar 10 '20 #1
2 3683
Ishan Shah
47 32bit
Here is the solution to your problem statement using Alias :

Expand|Select|Wrap|Line Numbers
  1. update p set p.TotalAmount = (select sum(amount) from ProcessTable as t where t.NAME = p.NAME
  2. AND t.Company = p.Company
  3. AND t.Age = p.Age
  4. AND t.Gender = p.Gender)
  5. FROM ProcessTable as p
Mar 16 '20 #2
12,516 Expert Mod 8TB
You should avoid keeping aggregates stored in a table like this. Instead, just use a view or query to get the aggregate data when needed. This way, your data is always up to date and won't get out of sync.
Mar 16 '20 #3

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

Similar topics

by: Jesse Sheidlower | last post by:
After some discussion in a separate thread, I've been trying to get a better understanding of the workings of multiple-column indexes, and think I'm still missing the point. I understand indexing...
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
by: Wishing I was skiing mom | last post by:
In form1, which is an item master maintenance screen, I have a button which launches form2. Form2 contains an item master datagrid. After the user double-clicks the desired item record in form2 I...
by: KemperR | last post by:
Dear All, I want to achieve that the combination of values over multiple columns in a table are unique. I know this works for SQL Server, but how do I declare this in an ACCESS 2000 data base...
by: the_kiddie98 | last post by:
Hi, I have indexed some information from a database using the dotNet port of Lucene. If I index a field as a keyword then Lucene does not pick it up when searching. If I index the field as text...
by: Sam | last post by:
Hi all I have a database table that has multiple column primary key and every thing looks fine when I load it in the datagrid web control. The problem is that the datagrid web control can only...
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect...
by: blizzardice | last post by:
I have a table that has a 3 field primary key. Company , Last Name first Name I have a combobox that lists all three and populates 3 textboxes based upon the selected item. The problem seems to be...
by: Bruce A. Julseth | last post by:
I'm "Failing" a trying to create a multicolum text input page. I have found many examples of two column pages where the first column is the field label and the second column is input text box. But,...
by: NakedEye | last post by:
I'd like to seek help with my access database. i would like to distribute value (format on currency) into multiple field base on date start and date end. for example on: if field Spend Amount...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

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.