How to count two different columns in T-SQL?

Question

I am playing with the StackOverflow datadump. Now I have a T-SQL Problem:

I can select a list with the number of questions per month and year with:

select datepart(year, posts.creationdate) as year,
datepart(month, posts.creationdate) as month, 
count(distinct posts.id) as questions
from posts
inner join posttags on posttags.postid = posts.id
inner join tags on tags.id = posttags.tagid
where posts.posttypeid = 1
group by datepart(month, posts.creationdate), 
datepart(year, posts.creationdate)
order by datepart(year, posts.creationdate), 
datepart(month, posts.creationdate)

If I add and tags.tagname = 'scala' on the WHERE-row, then I get the number of all "scala-questions". Is there any way I can show both the total number of questions and the number of questions containing a specific tag in the same result set (in different columns).

Because when I add the and tags.tagname = 'scala' I can no longer see the total number of questions per month.

Any ideas on how I can unit these resultsets into one?

Answer 1

If you use left outer join against posttags, count(posttags.tagid) will only count non null values. And since the left outer join only contain scala tags, you can skip the distinct in count(distinct posts.id).

select datepart(year, posts.creationdate) as year,
       datepart(month, posts.creationdate) as month,
       count(*) as questions,
       count(posttags.tagid) as sc
from posts
  left outer join posttags
    on posttags.postid = posts.id and
       posttags.tagid = (select id
                         from tags
                         where tagname = 'scala')
where posts.posttypeid = 1
group by datepart(month, posts.creationdate),
         datepart(year, posts.creationdate)
order by datepart(year, posts.creationdate),
         datepart(month, posts.creationdate)

Try here: https://data.stackexchange.com/stackoverflow/q/107948/