1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Chinese language

-- Tags: Spelling variants (singular/plural)

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
),
Pluralized as (
  select
    (TagName + 's') as vname,
    *
  from tags
)

select
  v1.TagName,
  c1.PostCount,
  case when c1.PostCount > c2.PostCount then '<-' else '->' end,
  v2.TagName,
  c2.PostCount
from
  Pluralized v1 inner join
  Tags v2       on v1.vname = v2.TagName
                inner join
  PostCounts c1 on v1.Id = c1.TagId
                inner join
  PostCounts c2 on v2.Id = c2.TagId
order by
  case when c1.PostCount > c2.PostCount then c1.PostCount else c2.PostCount end
  desc;

fork query

created aug 9 10 sth