Tag Quantity Usage Breakdown for Posts with [Tag]

1

Please login or register to vote for this query.

(click on this box to dismiss)

Breakdown of the number of tags used on posts with [tag].

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

-- Steps:
--   Pull TagId for [Tag]
--   Pull all posts with [Tag]
--   Pull number of tags used on those posts
--   Pull {tagTotal: COUNT(*)} from PostTags with [PostId]
--   GROUP BY [tagTotal]

DECLARE @TagId int = (  -- Get TagId of [tag] for use in SELECT
  SELECT
    t.Id
  FROM Tags t
  WHERE t.TagName = ##TagName:string##
)

SELECT
  tagTotals.TagTotal,
  COUNT(*) AS [Total Posts]
FROM (
  SELECT
    COUNT(*) AS [TagTotal]
  FROM (
    SELECT    -- Pull all posts with [Tag]
      p.Id AS [PostId]
    FROM PostTags pt
    JOIN PostsWithDeleted p ON p.Id = pt.PostId
    WHERE pt.TagId = @TagId
    GROUP BY p.Id
  ) postsWithTag
  JOIN PostTags pt ON pt.PostId = postsWithTag.PostId
  GROUP BY pt.PostId
) tagTotals
GROUP BY tagTotals.TagTotal
ORDER BY tagTotals.TagTotal

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results