User-defined variable in a query

Question

I've been working on a project that includes lots of summary statistics. One thing that would be very helpful for me would be to have a variable set equal to the result of a query that appeared on every line. Something like

declare @totalpop int
set @totalpop = select count(*) as [population]
from myTable
select sex, count(sex), @totalpop
from myTable

ninja edit on the query i tried, but it failed. Error is incorrect syntax near the keyword select

• Arnoud Kooi: Add parenthesis to the query where you set the variable (select count(*) as [population] from myTable )

Answer 1

Based on your last paragraph, this seems to be what you want:

SELECT tsum.*, sum(cnt) over (partition by NULL) as TotalPop
FROM (SELECT gender, count(*) as cnt
 FROM t
 GROUP BY gender
 ) tsum

The expression with the "over" clause is a window function that adds up all the windows.

• wootscootinboogie: This works just like I want. Is there not a way to approach the problem as I was trying to do?

Answer 2

SELECT gender, count(gender) AS countGender, @totalPop AS totalPop 
FROM myTable 
GROUP BY gender

Answer 3

SELECT gender, count(gender), @totalPop
FROM myTable 

Answer 4

Since you're using @ for variables, I assume you're using Sql Server. The following script returns a single row, with 3 columns (one for each count). I know it is not exactly what you requested, but I guess it is an easy way to return the same information:

DECLARE @COUNT_TOTAL INT;
DECLARE @COUNT_MALE INT;
DECLARE @COUNT_FEMALE INT;
SELECT @COUNT_TOTAL = COUNT(*)
 @COUNT_MALE = SUM(CASE WHEN gender='MALE' THEN 1 ELSE 0 END),
 @COUNT_FEMALE = SUM(CASE WHEN gender='FEMALE' THEN 1 ELSE 0 END),
FROM myTable;
SELECT @COUNT_TOTAL AS [COUNT_TOTAL]
 , @COUNT_MALE AS [COUNT_MALE]
 , @COUNT_FEMALE AS [COUNT_FEMALE];

• wootscootinboogie: I am indeed new to SQL Server. Could you point me to some literature why @ is bad for assigning variables?
• Gerardo Lima: Actually, using @ is the only way to create and use variables in T-SQL. The problem with your query was the syntax. I updated the sample to store the values into local variables.
• wootscootinboogie: Thanks, kind sir. I've only just begun using SQL