[sql-server] sql set variable using COUNT

I am trying to make a simple query to my server and want the result to be stored in the variable @times.

DECLARE @times int

SET @times = SELECT COUNT(DidWin)as "I Win"
FROM thetable
WHERE DidWin = 1 AND Playername='Me'

IntelliSense says Wrong syntax near Select

You can use SELECT as lambacck said or add parentheses:

SET @times = (SELECT COUNT(DidWin)as "I Win"
FROM thetable
WHERE DidWin = 1 AND Playername='Me');

You want:

DECLARE @times int

SELECT @times =  COUNT(DidWin)
FROM thetable
WHERE DidWin = 1 AND Playername='Me'

You also don't need the 'as' clause.

You can select directly into the variable rather than using set:

DECLARE @times int

SELECT @times = COUNT(DidWin)
FROM thetable
WHERE DidWin = 1 AND Playername='Me'

If you need to set multiple variables you can do it from the same select (example a bit contrived):

DECLARE @wins int, @losses int

SELECT @wins = SUM(DidWin), @losses = SUM(DidLose)
FROM thetable
WHERE Playername='Me'

If you are partial to using set, you can use parentheses:

DECLARE @wins int, @losses int

SET (@wins, @losses) = (SELECT SUM(DidWin), SUM(DidLose)
FROM thetable
WHERE Playername='Me');