Reputation: 2234
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
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