I would like to know the SQL coding to total records into a new field based on a string in a different field?

by Peter

I have a contributor table with amounts listed for several contributors. Each contributor has an ID associated with them. Some have made one payment, some have made several.

I would like to total the amounts for each contributor and add that total to another field. If you look at the picture I added, I would like to add the amounts that each contributor ID has given and put the totals in a new field called Total Contributor Gift. I.E. Contributor ID "1" would have a total of $606, Contributor ID "2" would have a total of $1327, etc. Thanks!

Comments for I would like to know the SQL coding to total records into a new field based on a string in a different field?

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 02, 2016

by: Anonymous

This is actually quite a straightforward problem, but lest's break it down into 2 parts.

Fisrt of all we'll determine the total amount paid by each contributor. Then we'll put that total back into the table in the new column total_contributor_gift.

Let's define our table and put some data in it:

CREATE TABLE contributor (contributor_id NUMBER, date_of_contribution DATE, contributor_gift NUMBER, total_contributor_gift NUMBER);
INSERT INTO contributor VALUES ( 1,'28-NOV-2009',350,NULL);
INSERT INTO contributor VALUES ( 1,'11-NOV-2010',250,NULL);
INSERT INTO contributor VALUES ( 2,'20-JAN-2010',335,NULL);
INSERT INTO contributor VALUES ( 2,'12-MAR-2010',340,NULL);
INSERT INTO contributor VALUES ( 2,'23-JUN-2010',150,NULL);
INSERT INTO contributor VALUES ( 2,'03-JUL-2010',373,NULL);
INSERT INTO contributor VALUES ( 2,'05-DEC-2010',129,NULL);

To determine the total from each contributor is a simp;e query as follows:

SELECT contributor_id , SUM(contributor_gift)
FROM contributor GROUP BY contributor_id ;

Now all we have to do is to put this total back into teh table in the total_contributor_gift column which obviously requires an UPDATE statement.
a plain UPDATE statement such as

UPDATE contributor SET total_contributor_gift = (SELECT SUM(contributor_gift) FROM contributor)

would put the total contributions from ALL contributors in the column though, What we need, therefore, is correlated UPDATE statement like this:

UPDATE contributor c1 SET total_contributor_gift =
(SELECT SUM(contributor_gift)
FROM contributor c2
WHERE c1.contributor_id =c2.contributor_id

This gives us the total per contributor but each row will have the same value for the total contribution.

cellpadding="2" cellspacing="2">




350 600

250 600

335 1327


335 1327


373 1327


129 1327

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.