A simple question about counting MS SQL

Question

I have this SQL

DECLARE @url varchar(100)
SET @url = 'http://mysite.com/%'

SELECT
[UserSessionSequenceID]
      ,[SiteID]
      ,[Referer]
      ,[Timestamp]
,ROW_NUMBER() over (PARTITION BY [Referer] ORDER BY referer DESC) AS sort 
  FROM [tblSequence]
WHERE [Referer] IS NOT NULL AND [Referer] NOT LIKE @url AND siteID = 15

Want to calculate a unique referent - the problem is that this SQL returns ALL matches and counts them one at a time. I only need a counter for each unique referencing (and still excluding @url with a similar one).

How to do it?

Answer 1

SELECT
      [Referer], Count([Referer]) as RefCount
  FROM [tblSequence]
WHERE [Referer] IS NOT NULL AND [Referer] NOT LIKE @url AND siteID = 15
GROUP BY [Referer]

Returning the order back (if it is really required) depends on your exact requirements.

Answer 2

Use DENSE_RANK not ROW_NUMBER if you need row data instead of aggregate