SELECT Tags.TagName, Tags.Count AS QuestionCount, SUM(Pos...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

SELECT
  Tags.TagName,
  Tags.Count AS QuestionCount,
  SUM(Posts.Score) AS NetQuestionScore,
  (SELECT
    (
    (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + 1.9208) / (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))) -
    1.96 * SQRT((CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) * CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))) / (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))) + 0.9604) /
    (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9)))) / (1 + 3.8416 / (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))))
  FROM Posts
  INNER JOIN Votes
    ON Posts.Id = Votes.PostId
  WHERE Posts.Id = ANY (SELECT
    a.PostId
  FROM PostTags a
  WHERE a.TagId = PostTags.TagId)
  HAVING CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) > 0)
  AS QuestionRatings,
  SUM(Posts.AnswerCount) AS AnswerCount,
  (SELECT
    COALESCE(SUM(a.Score), 0)
  FROM Posts a
  WHERE a.ParentId = ANY (SELECT
    b.PostId
  FROM PostTags b
  WHERE b.TagId = PostTags.TagId))
  AS NetAnswerScore,
  (SELECT
    (
    (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + 1.9208) / (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))) -
    1.96 * SQRT((CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) * CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))) / (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))) + 0.9604) /
    (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9)))) / (1 + 3.8416 / (CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 2 THEN 1
      ELSE 0
    END) AS numeric(19, 9)) + CAST(SUM(CASE
      WHEN Votes.VoteTypeId = 3 THEN 1
      ELSE 0
    END) AS numeric(19, 9))))
  FROM Posts
  INNER JOIN Votes
    ON Posts.Id = Votes.PostId
  WHERE Posts.ParentId = ANY (SELECT
    a.PostId
  FROM PostTags a
  WHERE a.TagId = PostTags.TagId)
  HAVING CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) > 0)
  AS AnswerRatings
FROM Tags
INNER JOIN PostTags
  ON Tags.Id = PostTags.TagId
INNER JOIN Posts
  ON PostTags.PostId = Posts.Id
  AND Posts.PostTypeId IN (1, 2, 3)
WHERE (SELECT
  (
  (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + 1.9208) / (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))) -
  1.96 * SQRT((CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) * CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))) / (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))) + 0.9604) /
  (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9)))) / (1 + 3.8416 / (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))))
FROM Posts
INNER JOIN Votes
  ON Posts.Id = Votes.PostId
WHERE Posts.Id = ANY (SELECT
  a.PostId
FROM PostTags a
WHERE a.TagId = PostTags.TagId)
HAVING CAST(SUM(CASE
  WHEN Votes.VoteTypeId = 2 THEN 1
  ELSE 0
END) AS numeric(19, 9)) + CAST(SUM(CASE
  WHEN Votes.VoteTypeId = 3 THEN 1
  ELSE 0
END) AS numeric(19, 9)) > 0)
IS NOT NULL
OR (SELECT
  (
  (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + 1.9208) / (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))) -
  1.96 * SQRT((CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) * CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))) / (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))) + 0.9604) /
  (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9)))) / (1 + 3.8416 / (CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 2 THEN 1
    ELSE 0
  END) AS numeric(19, 9)) + CAST(SUM(CASE
    WHEN Votes.VoteTypeId = 3 THEN 1
    ELSE 0
  END) AS numeric(19, 9))))
FROM Posts
INNER JOIN Votes
  ON Posts.Id = Votes.PostId
WHERE Posts.ParentId = ANY (SELECT
  a.PostId
FROM PostTags a
WHERE a.TagId = PostTags.TagId)
HAVING CAST(SUM(CASE
  WHEN Votes.VoteTypeId = 2 THEN 1
  ELSE 0
END) AS numeric(19, 9)) + CAST(SUM(CASE
  WHEN Votes.VoteTypeId = 3 THEN 1
  ELSE 0
END) AS numeric(19, 9)) > 0)
IS NOT NULL
GROUP BY PostTags.TagId,
         Tags.TagName,
         Tags.Count
ORDER BY Tags.TagName ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results