comparison SQL historical

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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)

Enter Parameters

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