Update same table with sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update same table with sum

Author

Topic

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-17 : 05:19:00

Good morning!

First of all, I apologise for this question as I know its so basic but I really cant work it out!

I have a table with columns
Serial PP1 PP2 PP3 PP4 -------upto-----PP250 PPTOTAL
1 1 1 0 1 ????
2 0 0 1 0 ????
3 0 0 0 0
4 1 1 1 1

All I want to do is add up the 250 "PP" columns to give a total! I have found examples using seperate tables and joins but cannot find a way to do this on the same table!!

As an aside, as I am very new to this, is it possible to use loops in SQL as obviously this would make the whole 1 to 250 part a lot neater?

Many thanks for your patience and help!

pootle_flump

1064 Posts

Posted - 2009-03-17 : 05:35:37

Sorry Paul - bad news.

>> I have found examples using seperate tables and joins but cannot find a way to do this on the same table!!
That's because that is the correct way to design the table. Your table is incorrectly designed - look at relational database design, normalisation and especially First Normal Form.

>> is it possible to use loops in SQL
Yes, but not advised

>> obviously this would make the whole 1 to 250 part a lot neater
I'm afraid it would not. It would require dynamic SQL, which is frequently an indicator of poor design (as in this case). T-SQL is a very poor "looper". It would also not be neat.

If you can't\ are not prepared to correct your design, "best" advice is to hard code the query:

SELECT PP1 + PP2 + PP3 + PP4.....

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-17 : 05:44:38

Thanks for your reply. Why is my table poorly designed? The values (PP1 - PP250) are used and are important variables. They are calculated from data in 3 other tables (it is survey data so a lot of columns!)
I dont see how else i could design the table structure.

pootle_flump

1064 Posts

Posted - 2009-03-17 : 05:50:05

I don't mean you don't store the data - you just don't store it like you would in a spreadsheet!

This is an excellent introduction to relational database design. Take 20 mins to read it, and focus especially on first normal form. Don't just skip to there!
http://www.tonymarston.net/php-mysql/database-design.html

The point is, properly designed your table would have 249 less columns, a lot more rows and the SQL would be trivial.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 05:53:20

A better design would be:

Serial PP_sr_no PP_value
1 1 1
1 2 1
1 3 0
...
2 1 0
2 2 0
2 3 1
..

Then it would have been a piece of cake to sum up the column values like this:

Select Serial, sum(PP_Value) as PPTotal
from tblFoo
group by Serial

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-17 : 06:03:45

OK, I see what you mean now! To be honest, I dont need the "data" stored, it is , as you rightly say just a calculation from other data tables. The problem is, I have only been using these tools for a few days so I am trying to follow examples, use code from books etc, etc.

Also, i am very much from a linear data background, and old habits die hard!!

If I post the full code, perhaps someone could advise me?!!

pootle_flump

1064 Posts

Posted - 2009-03-17 : 06:22:28

Two very common errors for new entrants are to think about design as per spreadsheets and SQL coding as per procedural languages. Read that link I posted - it might look like a lot but it is the minimum anyone should know before tackling database design. It will save you countless hours in the long run!

Then, once done, you might know the solution yourself. Else we can help you out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources