8bitcat
8bitcat

Reputation: 2234

t-sql return number of unique values in row

I have tried now for a couple of hours to return, a fourth row which would tell me the number of disctinct doman_namn. I keep trying to add a COUNT(DISTINCT doman_namn) but then i need a group by, which in turn destroys the purpose.

This is stored proc which takes two parameters:

@keyword[varchar](100),
@domannamn [varchar](100)

It uses two tables which looks like this

FIRST TABLE table t_doman columns; doman_id doman_name

SECOND TABLE table t_ranking columns; ranking_position ranking_date ranking_keyword ranking_id_doman

I use the @keyword to look up the correct rows in t_ranking table, there then i join on ranking_id_doman on doman_id to get the "doman_name", There will be a set of name's for every date, which in turn makes the names reapeat themselves for every date, i need to check how many distinct names there are and return that in a row.

I need it to work like:

Ranking position,   date,       name,  number of distinct name's
1___________________2012-11-11, tony,  3 
2___________________2012-11-11, chris, 3
3___________________2012-11-11, peter, 3
1___________________2012-11-10, tony,  3
2___________________2012-11-10, chris, 3
3___________________2012-11-10, peter, 3

 SELECT
 ranking_position,
 CONVERT(varchar(10),ranking_date, 120),
 doman_namn
 --Here's my my attempt COUNT(DISTINCT doman_namn) as 'number_of_discint_names'
 FROM 
     (SELECT 
      ranking_position,
      ranking_date,
      ranking_id_doman
      FROM dbo.t_ranking
      WHERE ranking_keyword = 'keyword'
      AND ranking_date BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE()
      AND ranking_id_doman IN (SELECT doman_id FROM dbo.t_doman WHERE doman_namn LIKE 'doman' + '%')) as tr
JOIN dbo.t_doman td on tr.ranking_id_doman = td.doman_id
--GROUP BY doman_namn ALSO IT DOES NOT WORK
ORDER BY ranking_date ASC

Upvotes: 0

Views: 195

Answers (1)

Laurence
Laurence

Reputation: 10976

Declare
  @keyword nvarchar(20) = N'keyword',
  @domannamn nvarchar(20) = N'C'

Select
  r.ranking_position,
  r.ranking_date,
  d.doman_name,
  Count(r.ranking_id_doman) Over (Partition By r.ranking_date)
From
  dbo.t_ranking r
    inner join
  dbo.t_doman d
    on r.ranking_id_doman = d.doman_id
Where
  ranking_keyword = @keyword And
  ranking_date Between DateAdd(day, -30, GetDate()) And GetDate() And
  d.doman_name like @domannamn + '%'
Order By
  2, 1

http://sqlfiddle.com/#!3/ccf10/2

As Richard points out, this doesn't work if you have duplicate ranking_id_doman values within a ranking date and you want the distinct ones.

Upvotes: 2

Related Questions