MS SQL variable add value from count in the same statement

Question

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?

Provide DBMS version tag. Later versions of sql-server have handy windowing functions . — Serg
– Serg, Commented Jul 4, 2017 at 7:50

Tim Schmelter · Accepted Answer · 2017-07-04 07:55:02Z

1

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.

answered Jul 4, 2017 at 7:55

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aster Tan Over a year ago

Hi Thanks for the reply! Is it going to be the same when you do UserCounter = UserCounter + (Count(*) OVER (Partition By Department)) inside the select statement?

Collectives™ on Stack Overflow

MS SQL variable add value from count in the same statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related