Har en tabell bo_MatchResult
Med 4 st fält MatchResultRound1 ... 4
Om MatchResultRound1 > 0 ... Round4 > 0 vill jag få ut summan av av dessa.

174 186 193 246 4
161 0 204 181 3
204 267 180 200 4
206 258 257 213 4
218 189 172 186 4
209 199 201 190 4
186 182 175 149 4
159 208 189 162 4
0 213 0 0 1

SELECT      
MatchResultRoundResult1, MatchResultRoundResult2, MatchResultRoundResult3, MatchResultRoundResult4,
(SELECT SUM(COUNT MatchResultRoundResult1 AND MatchResultRoundResult2 AND MatchResultRoundResult3 AND MatchResultRoundResult4) AS Ser
FROM bo_MatchResult WHERE MatchResultRound1 >0 AND MatchResultRoundResult2 >0 AND MatchResultRoundResult3 >0 AND MatchResultRoundResult4 >0)
FROM bo_MatchResult
WHERE     (bo_MatchResult.MatchResultMatchId = 2048075 AND bo_MatchResult.MatchResultHomeOrAwayTeam ='H')
ORDER BY bo_MatchResult.MatchResultHomeOrAwayTeam DESC

Såhär borde fungera:

SELECT      
MatchResultRoundResult1, MatchResultRoundResult2, MatchResultRoundResult3, MatchResultRoundResult4,
CASE
 WHEN MatchResultRoundResult1 > 0
  THEN 1
 ELSE 0
END
+
CASE
 WHEN MatchResultRoundResult2 > 0
  THEN 1
 ELSE 0
END
+
CASE
 WHEN MatchResultRoundResult3 > 0
  THEN 1
 ELSE 0
END
+
CASE
 WHEN MatchResultRoundResult4 > 0
  THEN 1
 ELSE 0
END
AS Summa

FROM bo_MatchResult
WHERE     (bo_MatchResult.MatchResultMatchId = 2048075 AND bo_MatchResult.MatchResultHomeOrAwayTeam ='H')
ORDER BY bo_MatchResult.MatchResultHomeOrAwayTeam DESC

Kanon!
Tack

Försöker få in det i min nuvarande SP:
Men får error Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

SELECT  bo_Match.MatchId, bo_MatchResult.MatchResultLicNbr, bo_Club.ClubName,  
        bo_Licence.FirstName + ' ' + bo_Licence.SurName AS SpelarNamn, bo_MatchResult.MatchResultShirtNo, 
        bo_MatchResult.MatchResultRoundResult1 + bo_MatchResult.MatchResultRoundResult2 + 
        bo_MatchResult.MatchResultRoundResult3 + bo_MatchResult.MatchResultRoundResult4
        AS MatchResultPlayer,  
        bo_MatchResult.MatchResultHomeOrAwayTeam, bo_MatchResult.MatchResultPlace, 
SUM(bo_MatchResult.MatchResultRankPoint1 + bo_MatchResult.MatchResultRankPoint2 + 
bo_MatchResult.MatchResultRankPoint3 + bo_MatchResult.MatchResultRankPoint4) AS Banp,
(SELECT bo_MatchResult.MatchResultRoundResult1, bo_MatchResult.MatchResultRoundResult2, 
bo_MatchResult.MatchResultRoundResult3, bo_MatchResult.MatchResultRoundResult4,
CASE
 WHEN bo_MatchResult.MatchResultRoundResult1 > 0
  THEN 1
 ELSE 0
END
+
CASE
 WHEN bo_MatchResult.MatchResultRoundResult2 > 0
  THEN 1
 ELSE 0
END
+
CASE
 WHEN bo_MatchResult.MatchResultRoundResult3 > 0
  THEN 1
 ELSE 0
END
+
CASE
 WHEN bo_MatchResult.MatchResultRoundResult4 > 0
  THEN 1
 ELSE 0
END
FROM bo_MatchResult) AS Summa
FROM         bo_Licence INNER JOIN
                      bo_MatchResult ON bo_Licence.LicNbr = bo_MatchResult.MatchResultLicNbr INNER JOIN
                      bo_Match ON bo_MatchResult.MatchResultMatchId = bo_Match.MatchId INNER JOIN
                      bo_Hall ON bo_Match.MatchHallId = bo_Hall.HallId INNER JOIN
                      bo_Division ON bo_Match.MatchDivisionId = bo_Division.DivisionId INNER JOIN
                      bo_Club ON bo_MatchResult.MatchResultTeamId = bo_Club.ClubId
WHERE     (bo_MatchResult.MatchResultMatchId = 2048075 AND bo_MatchResult.MatchResultHomeOrAwayTeam ='H')
GROUP BY bo_Match.MatchId,bo_MatchResult.MatchResultLicNbr, bo_Club.ClubName,bo_Licence.FirstName, bo_Licence.SurName,
bo_MatchResult.MatchResultShirtNo,bo_MatchResult.MatchResultRoundResult1,
bo_MatchResult.MatchResultRoundResult2,bo_MatchResult.MatchResultRoundResult3,
bo_MatchResult.MatchResultRoundResult4,
bo_MatchResult.MatchResultHomeOrAwayTeam, bo_MatchResult.MatchResultPlace
ORDER BY bo_MatchResult.MatchResultHomeOrAwayTeam DESC, bo_MatchResult.MatchResultShirtNo