Showing row counts with commas

I wanted to display a changing row-count (in a staging table) including commas (eg: 1,234 or 1,234,567).

I tried using STUFF() in a CASE statement to insert commas, then LEFT(), before settling on this, which is more succinct, and scales from 1 to 1,999,999,999

select replace(convert(varchar(20), convert(money, count(*)), 1), '.00', '')
from dbo.sometable
with(nolock)

To explain how it works …

– the ‘1’ is a style of VARCHAR that includes commas when converting from MONEY
– then REPLACE removes the ‘.00’ also added during the conversion from MONEY