CODEWITHSUNDEEP
sqlcount
timothy.s.lau
timothy.s.lau

Reputation: 1101

How to count instances in SQL

I have a table like:

enter image description here

But I want to count the instances of my duplicate id's: enter image description here

Any help would be appreciated.

Upvotes: 0

Views: 207

Answers (2)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Use ROW_NUMBER() function

SELECT id,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS idcnt,
       x1,
       x2
  FROM tablename

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 12969

This will be the syntax in MSSQL

 CREATE TABLE #test(id int , x1 int, x2 int)

 INSERT INTO #test(id,x1,x2) VALUES (1,1,1), (2,1,1), (2,2,2);

 SELECT id
        ,ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)
        ,x1 
        ,x2
 FROM #test

Upvotes: 2

Related Questions