Tough SQL query... 1

[Jen53403]

I have a database of people and their money contributions. I have 6 columns to keep track of individual contributions, and one column called TotalContribution. I want to write a query that would set TotalContribution equal to the sum of the 6 contribution amounts for each person in the table. How can I write a query to do that? I'm writing SQL for Access. Thanks!!

 

[PWise]

updateb tablename
set TotalContribution = nz(colum1,0)+nz(colum2,0)+nz(colum3,0)+nz(colum4,0)+nz(colum5,0)+nz(colum5,0)

 

[SQLDenis]

pwise ,

nz should be ISNULL or COALESCE, last column should be column 6

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[PWise]

sorry! denis your right

on the other hand perhaps jen is writeing sql for access

I'm writing SQL for Access.

So. jen if this is for access the nz is right ,but this is a Sql fourm if this is for SQL Server then use ISNULL or COALESCE

 

[SQLDenis]

Sorry, i missed the access part

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Jen53403]

Thanks guys! That's all I needed.

 

[ESquared]

Of course, if it's Access, the question belongs in the Access forum, not the SQL server forum.

 

[Jen53403]

Sorry ESquared, I didn't realize there was a difference. I thought this forum was for anything coded in SQL.

 

[JoeAtWork]

I have 6 columns to keep track of individual contributions, and one column called TotalContribution.

The thing that makes this a "tough" SQL query is the unnormalized nature of the database. The Individuals and the Contributions should be in separate tables. What will you do when someone makes a seventh contribution?

The wrong answer is "add another column". Because now you will have to update everything (such as your TotalContribution above) that assumes there are only 6 columns.

Far better and more flexible to be able to have an SQL statment like:

SELECT IndividualID, Sum(Contribution) AS TotalContribution FROM Contributions GROUP BY IndividualID

See, now it doesn't matter if someone made 1, 2, 8, or 50 contributions, you will always get the correct sum, and you never have to change your database schema because you need to add more data.

 

[ESquared]

Thanks Joe for taking the time to give the answer we all knew but didn't feel like expending the effort to provide.