CODEWITHSUNDEEP
sql-servert-sql
Aster Tan
Aster Tan

Reputation: 67

MS SQL variable add value from count in the same statement

I have this Select statement

select Id, UserName, from UserTable where Department= @DepartmentInput

and a variable to calculate how many users from the department

@UserCounter

Is there a way to add count(the number of users from the department) into @UserCounter from the select statement should the statement runs in a while loop?

Upvotes: 0

Views: 57

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

Presuming sql-server:

select Id, UserName, @UserCounter = Count(*) OVER (Partition By Department)
from UserTable 
where Department= @DepartmentInput

Otherwise a simple sub-query should work too:

select Id, UserName, @UserCounter = (select count(*) from UserTable
                                     where Department= @DepartmentInput)
from UserTable 
where Department= @DepartmentInput

The database should be clever enough to optimize that query so that the subquery doesn't need to be avaulated for every (matching) row.

Upvotes: 1

Related Questions