sql set variable using COUNT
Sql ServerTsqlSql Server Problem Overview
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
Sql Server Solutions
Solution 1 - Sql Server
You just need parentheses around your select:
SET @times = (SELECT COUNT(DidWin) FROM ...)
Or you can do it like this:
SELECT @times = COUNT(DidWin) FROM ...
Solution 2 - Sql Server
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');
Solution 3 - Sql Server
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.
Solution 4 - Sql Server
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');
Solution 5 - Sql Server
If you need to print the result with column name "c", or read the result in your code by using "c" column name:
DECLARE @c int;
SELECT c = count(*) from '%Table%' WHERE '%condition%'