CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2sql-server-2012
Hiren gardhariya
Hiren gardhariya

Reputation: 1247

count detail record and display in new column in sql server

i am using sql server 2008, in which i have some trouble i can not find one column

TblMaster 
  ID      Name      City     
  1       Hiren     Juanagadh
  2       Ashish    Gandhinagar
  2       Mayur     Ahmedabad
  3       Hitesh    Junagadh
  4       Nipun     Ahmedabad
  4       Vivek     Rajkot
  4       Samir     Surat
  5       Sagar     Vadodara

Now i want Anoter column CountId so i want output like below

TblMaster 
  ID      Name      City           CountId 
  1       Hiren     Juanagadh       0
  2       Ashish    Gandhinagar     2
  2       Mayur     Ahmedabad       2
  3       Hitesh    Junagadh        0
  4       Nipun     Ahmedabad       3
  4       Vivek     Rajkot          3
  4       Samir     Surat           3
  5       Sagar     Vadodara        0

Means if Id column only one then CountId = 0 If Id column more than one then CountId = Count of Idcolumn

Upvotes: 0

Views: 108

Answers (2)

DimaSUN
DimaSUN

Reputation: 921

Prepare table

declare @T table ( 
           id int, 
           Name nvarchar(6), 
           City nvarchar(20))

insert @T values 
(  1 ,      'Hiren',     'Juanagadh'),
(  2 ,      'Ashish',    'Gandhinagar'),
(  2 ,      'Mayur',     'Ahmedabad'),
(  3 ,      'Hitesh',    'Junagadh'),
(  4 ,      'Nipun',     'Ahmedabad'),
(  4 ,      'Vivek',     'Rajkot'),
(  4 ,      'Samir',     'Surat'),
(  5 ,      'Sagar',     'Vadodara')

Select statement

without 1->0 correction

SELECT *, CountID  = count(*) over (Partition by ID)
from @T

with 1->0 correction

select id, Name,City,CountID =  case when CountID = 1 then 0 else CountID end
 from (
SELECT *, CountID  = count(*) over (Partition by ID)
from @T )

RES

Upvotes: 1

pankeel
pankeel

Reputation: 1148

Try this query:::

select *,(case when (select count(id) from TblMaster )=1 
          then 0 else (select count(id) from TblMaster) end) as count 
from tblmaster

Upvotes: 1

Related Questions