1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

SELECT 
  dateadd(year, datediff(year, 0, posthistory.creationdate), 0), 
  sum(case 
        when posthistory.posthistorytypeid = 1 
          AND posts.Title LIKE '%sql%'
          then 1 
        else 0 end) as [SQL asked total],
  sum(case 
        when posthistory.posthistorytypeid = 1 
          AND posts.Title LIKE '%mysql%'
          then 1 
        else 0 end) as [MySQL asked],
  sum(case 
        when posthistory.posthistorytypeid = 1 
          AND posts.Title LIKE '%postgres%'
          then 1 
        else 0 end) as [Postgres asked],
  sum(case 
        when posthistory.posthistorytypeid = 1 
          AND posts.Title LIKE '%presto%'
          then 1 
        else 0 end) as [Postgres asked],
  sum(case 
        when posthistory.posthistorytypeid = 1 
          AND posts.Title LIKE '%redshift%'
          then 1 
        else 0 end) as [Postgres asked],
  sum(case 
        when posthistory.posthistorytypeid = 1 
          AND posts.Title LIKE '%python%'
          then 1 
        else 0 end) as [Python asked]
FROM posthistory
INNER JOIN posts ON posthistory.postid = posts.id
INNER JOIN users ON users.Id = posts.OwnerUserId
WHERE posthistory.creationdate >= '1/1/2012' 
AND posts.posttypeid = 1
GROUP BY 
dateadd(year, datediff(year, 0, posthistory.creationdate), 0)
ORDER BY 
dateadd(year, datediff(year, 0, posthistory.creationdate), 0)

fork query

edited jul 18

created jul 17 mmira