0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

SELECT
  CAST(CAST(DATEPART(yyyy, p.CreationDate) AS NVARCHAR(4)) + '-' + CAST(DATEPART(mm, p.CreationDate) AS NVARCHAR(2)) + '-01' AS DATETIME) AS [Date],
  Count(*) AS [Count],
  Avg(p.ViewCount) AS [Avg views],
  Stdev(p.ViewCount) AS [Std dev],
  Avg(IIF(p.Score > 0, p.ViewCount, NULL)) AS [Score >0] --,
  --Avg(CASE p.ClosedDate WHEN NULL THEN NULL ELSE p.ViewCount END) AS [Open]
FROM Posts p
WHERE p.PostTypeId = 1
GROUP BY CAST(CAST(DATEPART(yyyy, p.CreationDate) AS NVARCHAR(4)) + '-' + CAST(DATEPART(mm, p.CreationDate) AS NVARCHAR(2)) + '-01' AS DATETIME)
ORDER BY CAST(CAST(DATEPART(yyyy, p.CreationDate) AS NVARCHAR(4)) + '-' + CAST(DATEPART(mm, p.CreationDate) AS NVARCHAR(2)) + '-01' AS DATETIME) DESC

fork query

edited aug 7 14

created aug 1 14 Gilles