Replace and Group By in SQL

Question

HI I want to group my results by a field that has had characters replaced but also keep the original value.

This is what I have but I need to return the original URL value without changing the results.

select Count(ID) as TotalClicks, Replace(URL, '/', '') as DistinctURL
  from email_click_throughs
 where emailid = @ID
group by  Replace(URL, '/', '')
order by TotalClicks desc

Answer 1

This would give you what you described, but it makes the grouping on "Replace(URL..." redundant, so I may be misunderstanding:

select Count(ID) as TotalClicks, URL as DistinctURL
from email_click_throughs
where emailid = @ID
group by Replace(URL, '/', ''), URL
order by TotalClicks desc

If you just want to get back one URL even when two are equivalent after removing '/', you could do something like

select Count(ID) as TotalClicks, Max(URL) as DistinctURL 
from email_click_throughs 
where emailid = @ID group by Replace(URL, '/', '') 
order by TotalClicks desc

But you don't have much control over which of your "matches" is getting dropped.