Search code examples
sqlsql-servercountmultiple-columns

Count advanced in SQL Server group by 1 column

I have 1 table like the picture

enter image description here

I want to count column number the output as follows

enter image description here

I try but it not show column ID and name as I want.

select id, name, number, count(number)
from table_demo
group by id, name, number

My result

enter image description here

Can't help you with the SQL Server 2019

Solution

  • Something like

    select id, name, number, count(*) over (partition by number)
from table_demo order by 1,2

    For future reading Microsoft Documentation and SQL Count Function with Partition By Clause