Sum up columns and rows??

[DOGWATCH]

Stuck on a problem, I have this query

select i.vchUser3, i.vchUser5, i.vchUser6, i.vchUser7
from dbo.Incident i INNER JOIN dbo.Company ON i.iOwnerID = dbo.Company.iCompanyId
where (i.dtInsertDate BETWEEN '3/1/2004' AND '3/31/2004') AND dbo.Company.vchUser2 In (101533,101652)
and I.iIncidentCategory = 3 and i.vchUser3 is not null

which will give me a result like

vchUser3 vchUser5 vchUser6 vchUser7
--------- ----------- --------- --------
0 0 0 0
5 0 0 0
0 3 0 0
0 0 0 10
0 20 0 0
25 0 0 0


What i need is to be able to sum up the
values of vchUser3 + vchUser5 + vchUser6 + vchUser7
for all rows. These values are also in the database
as strings so they need to formated into integers to
be add them up. I also need to filter out rows with
NULL which is demonstrated in my query.

the result would be 5+25+3+20+10= 63

Is there someway I can change the query so it will
just give me a sum of the all values in the rows
(not the count of rows) ???

 

[snag56]

How about:

select sum(i.vchUser3) + sum(i.vchUser5) + sum(i.vchUser6) + sum(i.vchUser7) as total_all
from dbo.Incident i

 

[DOGWATCH]

gives me this error

The sum or average aggregate operation cannot take a varchar data type as an argument.

vchUser3 is a field type varchar but should be represented as a number.

 

[DOGWATCH]

This works but ...

select 
SUM(Convert( int, LEFT(i.vchUser3,2))) +
SUM(Convert( int, LEFT(i.vchUser5,2))) +
SUM(Convert( int, LEFT(i.vchUser6,2))) +
SUM(Convert( int, LEFT(i.vchUser7,2))) AS Tot
from dbo.Incident i INNER JOIN dbo.Company ON i.iOwnerID = dbo.Company.iCompanyId
where (i.dtInsertDate BETWEEN '3/1/2004' AND '3/31/2004') AND dbo.Company.vchUser2 In (101533,101652)
and I.iIncidentCategory = 3 and i.vchUser3 is not null

on occasion I get these weird values in my table like
0 0 which just means 0.

my code works as long as a value is less than 100. I also cannot filter out all rows that give 0 0 because it may have another column like

col1 col2 col3 col4
0 0 25 0 0

it will throw off my sum value if that filter out rows with '0 0'

any ideas?