0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for folks dedicated to a lifestyle that can be maintained indefinitely without depleting available resources

-- Tags: Spelling variants (AE/BE: o/ou)

with
PostCounts as (
  select
     Tags.Id         as TagId,
     count(Posts.Id) as PostCount
  from Posts    inner join
       PostTags on Posts.Id = PostTags.PostId
                inner join
       Tags     on PostTags.TagId = Tags.Id
  group by Tags.Id
),
Variants as (
  select *
  from (select replace(TagName, 'ou', 'o') as vname, * from tags) as r
  where vname != tagname
)

select
  Tags.TagName,
  tc.PostCount,
  Variants.TagName,
  vc.PostCount
from
  Tags          inner join
  Variants      on Tags.tagName = Variants.vname
                inner join 
  PostCounts tc on Tags.Id = tc.TagId
                inner join
  PostCounts vc on Variants.Id = vc.TagId
order by vc.PostCount desc;

fork query

created aug 8 10 sth