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.
| Author |
Topic |
|
starnold
Yak Posting Veteran
83 Posts |
 Posted - 2008-06-17 : 07:02:03
|
| I have a database that has several columns of product spend data that I need to add together.Example:URN Prod1 Prod2 Prod3 Prod41 500 500 500 5002 750 750 750 7504 100 400 800 5005 300 600 500 5006 200 700 400 500I would like to add Prod1 & Prod2 totals together and Prod3 & Prod4 totals together to give me 2 unique columns with the total value? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 07:09:09
|
Why not try to do exactly as you have said?selectURN,sum(Prod1+Pro2) as Sum1,sum(prod3+Prod4) as Sum2from tablegroup by URN |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-17 : 07:11:14
|
quote:
Originally posted by RickD
Why not try to do exactly as you have said?selectURN,sum(Prod1+Pro2) as Sum1,sum(prod3+Prod4) as Sum2from tablegroup by URN
Is the group by required? the sample data doesnt seem like having multiple records with same URN value. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 07:16:42
|
No, but you can guarantee it will be..  Actually, yes it is if you want to use sum(), but not if you just take the sum() out. If you can guarantee 1 row for each URN then you can just do:selectURN,Prod1+Prod2 as Sum1,prod3+Prod4 as Sum2from table |
|
|
|
starnold
Yak Posting Veteran
83 Posts |
Posted - 2008-06-17 : 07:26:00
|
| thanks I assumed it was as easy as that but wanted clarification. How can I get it to avoid not summing the nulls? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 07:40:44
|
| put an isnull(<field>,0) around each field. |
|
|
|
|
|
|
Summating Columns