Need to SUM the values in a row
I've got a stored procedure which is working well, it's purpose is to check whether or not a user has enabled or disabled certain parts of a dashboard. The fields are all tinyint using 1/0 and I'd like to be able to add up those fields to give me a total number of active items. Here's what I've got so far:
SELECT userShowProcess, userShowLEN, userShowContractExp, userShowWO, userShowTSO, userShowOcc, userShowRev, userShowDelinq, userShowExp
FROM tblUsers
WHERE userID = @UserID;
I'd like to get another field at the end (something like profileOptionsCount) that would add up all the fields that are set to "1" like so:
SELECT userShowProcess, userShowLEN, userShowContractExp, userShowWO, userShowTSO, userShowOcc, userShowRev, userShowDelinq, userShowExp
FROM tblUsers
WHERE userID = @UserID;
I'd like to get another field at the end (something like profileOptionsCount) that would add up all the fields that are set to "1" like so:
userShowProcess userShowLEN userShowWO userShowTSO userShowOcc userShowRev userShowDelinq userShowExp profileOptionsCount
--------------- ----------- ---------- ----------- ----------- ----------- -------------- ----------- -------------------
1 0 1 1 1 0 0 0 4
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect -- I had no idea it was that simple! Thanks very much!
SELECT userShowProcess,
userShowLEN,
userShowContractExp,
userShowWO,
userShowTSO,
userShowOcc,
userShowRev,
userShowDelinq,
userShowExp,
userShowProcess + userShowLEN + userShowContractExp + userShowWO + userShowTSO +
userShowOcc + userShowRev + userShowDelinq + userShowExp profileOptionsCount
FROM tblUsers
WHERE userID = @UserID;