Get number of posts per month in data.stackexchange

Question

I'm trying to get the total monthly number of posts per month

WITH QuestStatsByMonth  AS (
    SELECT  DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0) AS [Month],
    t.TagName,
    SUM (q.Score)               AS TotalScore,
    q.Id                        AS id

    FROM        Posts           q
    INNER JOIN  PostTags        pt
    ON          q.Id            = pt.PostId
    INNER JOIN  Tags            t
    ON          t.Id            = pt.TagId

    WHERE       q.PostTypeId    = 1
    AND         t.TagName       IN ('perl6')

    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0),
                t.TagName
)
SELECT
q.[Month], q.TagName + ' questions' AS [Tag], COUNT(q.id) AS Qs

FROM        QuestStatsByMonth   q
LEFT JOIN   QuestStatsByMonth   h
ON          h.[Month]           <= q.[Month]
AND         h.TagName           = q.TagName
GROUP BY    q.[Month], q.TagName
ORDER BY    q.[Month], q.TagName

But this issues a

Column 'Posts.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have tried to make COUNT in the WITH statement, but then the error is somewhere else. Any idea?

[SEDE Demo2](http://data.stackexchange.com/stackoverflow/query/847859)?? — Lukasz Szozda, May 05 '18 at 06:33

score 1 · Answer 1 · edited Jan 18 '21 at 12:32

You need to wrap q.id with agg function:

WITH QuestStatsByMonth  AS (
    SELECT DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0) AS [Month],
    t.TagName,
    SUM (q.Score)               AS TotalScore,
    COUNT(q.Id)                 AS id

    FROM  Posts           q
    JOIN  PostTags        pt
      ON  q.Id            = pt.PostId
    JOIN  Tags            t
      ON  t.Id            = pt.TagId
    WHERE q.PostTypeId    = 1
      AND t.TagName       IN ('perl6')
    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0),
                t.TagName
)
SELECT
  q.[Month], q.TagName + ' questions' AS [Tag],
  SUM(q.id) AS Qs
FROM        QuestStatsByMonth   q
LEFT JOIN   QuestStatsByMonth   h
  ON          h.[Month]           <= q.[Month]
  AND         h.TagName           = q.TagName
GROUP BY    q.[Month], q.TagName
ORDER BY    q.[Month], q.TagName;

SEDE Demo

score 1 · Accepted Answer · answered May 05 '18 at 06:35

1

Get rid of these bits:

SUM (q.Score)               AS TotalScore,

and

GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0),
            t.TagName

Within your CTE, you just filter questions. You shouldn't actually group yet if grouping is something you do in your main query. Grouping makes it impossible to select q.Id: you asked for one row for each month/tag, but there are generally multiple question IDs in each month/tag. You can just drop TotalScore without replacing it because you don't use it anyway.

answered May 05 '18 at 06:35

This works, but the numbers do not add up [It returns up to thousands of questions](http://data.stackexchange.com/stackoverflow/query/847857/question-count-or-score-growth-over-time-by-tag-comparison), when the total by tag is around 600 – jjmerelo May 05 '18 at 09:37
1

@jjmerelo That's a result of your `LEFT JOIN`, which effectively turns the result into sort of a cumulative sum. I see now that it doesn't even work properly for that. You can take it out (as in Bogdan's answer) since you don't use anything from `h`. – May 05 '18 at 09:47

score 1 · Answer 3 · answered May 05 '18 at 06:37

I removed the LEFT JOIN part.

WITH QuestStatsByMonth  AS (
    SELECT DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0) AS [Month],
    t.TagName,
    SUM (q.Score)               AS TotalScore,
    COUNT(q.Id)                        AS id

    FROM  Posts           q
    JOIN  PostTags        pt
      ON  q.Id            = pt.PostId
    JOIN  Tags            t
      ON  t.Id            = pt.TagId
    WHERE q.PostTypeId    = 1
      AND t.TagName       IN ('perl6')
    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0),
                t.TagName
)
SELECT
  q.[Month], q.TagName + ' questions' AS [Tag],
  COUNT(q.id) AS Qs
FROM        QuestStatsByMonth   q
GROUP BY    q.[Month], q.TagName
ORDER BY    q.[Month], q.TagName

Looks like what you are asking.

Get number of posts per month in data.stackexchange

3 Answers3