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
(California)


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
Rating
starstarstarstarstar

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">





































CONTRIBUTOR_ID CONTRIBUTOR_GIFT

TOTAL_CONTRIBUTOR_GIFT

1

350 600
1

250 600
2

335 1327

2

335 1327

2

373 1327

2

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.