Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

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:
userShowProcess   userShowLEN   userShowWO   userShowTSO   userShowOcc   userShowRev   userShowDelinq   userShowExp   profileOptionsCount
---------------   -----------   ----------   -----------   -----------   -----------   --------------   -----------   -------------------
1		  0		1            1             1             0             0                0	      4

Open in new window

0
saoirse1916
Asked:
saoirse1916
  • 2
1 Solution
 
Anthony PerkinsCommented:
SELECT      userShowProcess,
      userShowLEN,
      userShowContractExp,
      userShowWO,
      userShowTSO,
      userShowOcc,
      userShowRev,
      userShowDelinq,
      userShowExp,
      userShowProcess + userShowLEN + userShowWO + userShowTSO +
      userShowOcc + userShowRev + userShowDelinq + userShowExp profileOptionsCount
FROM      tblUsers
WHERE      userID = @UserID;
0
 
Anthony PerkinsCommented:
By the way you skipped userShowContractExp, was that intentional, otherwise:

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;
0
 
saoirse1916Author Commented:
Perfect -- I had no idea it was that simple!  Thanks very much!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now