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
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.
SELECT gender, count(gender) AS countGender, @totalPop AS totalPop
FROM myTable
GROUP BY gender
SELECT gender, count(gender), @totalPop
FROM myTable
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];
@ 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.