Getting the SUM of two field values [modified]
-
Hi, I'm trying to get the sum of two fields: declare @totalsum money set @totalsum = (sum(fieldname1 + fieldname2)) the error message displays an invalid fieldname for both fields, i have the fieldnames spelled correctly but still get the error. Any suggestions would be great. Thanks, Nino Italy == World Champions -- modified at 15:06 Monday 10th July, 2006
-
Hi, I'm trying to get the sum of two fields: declare @totalsum money set @totalsum = (sum(fieldname1 + fieldname2)) the error message displays an invalid fieldname for both fields, i have the fieldnames spelled correctly but still get the error. Any suggestions would be great. Thanks, Nino Italy == World Champions -- modified at 15:06 Monday 10th July, 2006
I'm not sure what you are trying to do, but if you want the sum of all (fieldname1 + fieldname2) values for every record in a particular table, then you need to supply the table name and change the word
settoselect.declare @totalsum money
select @totalsum = (sum(fieldname1 + fieldname2))
from tablenameBut most likely, you want the sum of these two fields for a particular row, so you would need a where clause:
declare @totalsum money
select @totalsum = fieldname1 + fieldname2 from tablename
where primarykeyfield = 1or something... --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
I'm not sure what you are trying to do, but if you want the sum of all (fieldname1 + fieldname2) values for every record in a particular table, then you need to supply the table name and change the word
settoselect.declare @totalsum money
select @totalsum = (sum(fieldname1 + fieldname2))
from tablenameBut most likely, you want the sum of these two fields for a particular row, so you would need a where clause:
declare @totalsum money
select @totalsum = fieldname1 + fieldname2 from tablename
where primarykeyfield = 1or something... --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Ok, I should have posted the entire code block, I tried the method listed above with the rest of the SELECT statement and get an error message as follows. Server: Msg 141, Level 15, State 1, Line 4 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. Here is the entire code block:
declare @totalsum money
select @totalsum = (sum(d.amount + d.debit)), a.officer, d.amount,d.debit, e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype, a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow inner join e120 d on d.escrow = e.escrow and a.officer is not null order by e.open_dateThanks Nino
-
Ok, I should have posted the entire code block, I tried the method listed above with the rest of the SELECT statement and get an error message as follows. Server: Msg 141, Level 15, State 1, Line 4 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. Here is the entire code block:
declare @totalsum money
select @totalsum = (sum(d.amount + d.debit)), a.officer, d.amount,d.debit, e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype, a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow inner join e120 d on d.escrow = e.escrow and a.officer is not null order by e.open_dateThanks Nino
Yeah, you can't do that. If you want to sum all amount and debit columns for the entire table...then you wouldn't also pull back all the other fields. If you want to just add amount and debit together for each record, then you don't need a sum aggregate function.
select d.amount + d.debit as recsum, a.officer, d.amount,d.debit,
e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype,
a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow
inner join e120 d on d.escrow = e.escrow and a.officer is not null
order by e.open_datedeclare @totalsum money
select @totalsum = sum(d.amount + d.debit)
from escrow e inner join a10 a on e.escrow = a.escrow
inner join e120 d on d.escrow = e.escrow and a.officer is not nullor you could combine them like this - and every record will have a column with the TotalSum in it:
select (select sum(d2.amount + d2.debit)
from escrow e2 inner join a10 a2 on e2.escrow = a2.escrow
inner join e120 d2 on d2.escrow = e2.escrow and a2.officer is not null) as totalsum,
d.amount + d.debit as recsum, a.officer, d.amount,d.debit,
e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype,
a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow
inner join e120 d on d.escrow = e.escrow and a.officer is not null
order by e.open_date--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Yeah, you can't do that. If you want to sum all amount and debit columns for the entire table...then you wouldn't also pull back all the other fields. If you want to just add amount and debit together for each record, then you don't need a sum aggregate function.
select d.amount + d.debit as recsum, a.officer, d.amount,d.debit,
e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype,
a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow
inner join e120 d on d.escrow = e.escrow and a.officer is not null
order by e.open_datedeclare @totalsum money
select @totalsum = sum(d.amount + d.debit)
from escrow e inner join a10 a on e.escrow = a.escrow
inner join e120 d on d.escrow = e.escrow and a.officer is not nullor you could combine them like this - and every record will have a column with the TotalSum in it:
select (select sum(d2.amount + d2.debit)
from escrow e2 inner join a10 a2 on e2.escrow = a2.escrow
inner join e120 d2 on d2.escrow = e2.escrow and a2.officer is not null) as totalsum,
d.amount + d.debit as recsum, a.officer, d.amount,d.debit,
e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype,
a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow
inner join e120 d on d.escrow = e.escrow and a.officer is not null
order by e.open_date--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters