1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

with q as (
  select count(*) as cnt, t.id as tagid, t.tagname, v.votetypeid
  from (select top 1000 * from tags t order by t.count desc) as t
    join posttags pt on pt.tagid = t.id
    join posts p on p.id = pt.postid
    join votes v on v.postid = p.id
  where v.votetypeid in (2, 3) and p.posttypeid = 1
  group by t.id, t.tagname, v.votetypeid
)
select q.tagname AS [Tag], q.tagid AS [Tag ID], q.cnt AS [Upvotes], qq.cnt AS [Downvotes], ROUND(100.0 * qq.cnt / NULLIF(q.cnt, 0), 2) AS [D/U ratio]
from q
join q qq on q.tagname = qq.tagname and qq.votetypeid = 3
where q.votetypeid = 2
order by q.tagname

fork query

edited dec 14

created dec 7 jon.doe35042