Partition Count with slightly different names

• npatel565

  SSC Veteran

  Points: 202

  More actions

  April 6, 2010 at 12:02 pm

  #233475

  Hey guys, this is a very different tsql question. I have the following ddl

  create table #test1

  (

  id int,

  name varchar(50)

  )

  insert into #test1 (id, name)

  select 1, 'apple 1' union all

  select 1, 'apple 1' union all

  select 1, 'apple 2' union all

  select 2, 'orange 1' union all

  select 2, 'orange 1' union all

  select 2, 'orange 2' union all

  select 2, 'orange 3' union all

  select 3, 'banana 2' union all

  select 3, 'banana 3' union all

  select 3, 'banana 3' union all

  select 3, 'banana 2' union all

  select 3, 'banana 1'

  I have the following query below

  select id, name,

  row_number() over (partition by id order by name asc) as NumberingfProductWithSameTitle,

  count(*) over (partition by name) as NumberOfProductsWithSameTitle from #test1

  when you run this query you see that for the "NumberOfProductsWithSameTitle" column for "apple" it shows 2, 2, 1. This is because "apple 1" occurs twice and "apple 2" occurs once. Now for the question, is there a way to make the "NumberOfProductsWithSameTitle" column for "apple" show 3, 3, 3? Because there are 3 instances of "apple" even though the names of the apples are slightly different i.e "apple 1", "apple 2"

• Paul White

  SSC Guru

  Points: 150467

  More actions

  April 6, 2010 at 12:48 pm

  #1146718

  Seems an odd question, but anyway:

  select id,

  name,

  row_number() over (partition by id order by name asc) as NumberingfProductWithSameTitle,

  count(*) over (partition by left(name, charindex(space(1), name))) as NumberOfProductsWithSameTitle

  from #test1

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• npatel565

  SSC Veteran

  Points: 202

  More actions

  April 6, 2010 at 12:59 pm

  #1146730

  Thanks Paul, you have been very helpful as always.