开发者

combine two queries that count by month

Inspired by this question on Meta, I wrote two queries on the Stack Exchange Data Explorer, one that counts the total number of Questions Asked by Month on SO and another that counts the Bounties Awarded by Month. How can I combine them so that I have the output in one query? I'd like to see Year, Month, Questions, Bounties, an开发者_StackOverflowd Amount in one report.

Questions are recorded in the Posts table where PostTypeId = 1, but bounties are recorded in the Votes table where VoteTypeId = 9.


I wrote this in notepad & haven't worked with data explorer on SO.

select Isnull(V.Year, P.Year) As Year,
Isnull(V.Month, P.Month) As Month,
Isnull(V.Bounties, 0) As Bounties,
Isnull(V.Amount,0) As Amount ,
P.Questions
FROM
(
select
datepart(year, Posts.CreationDate) Year,
datepart(month, Posts.CreationDate) Month,
count(Posts.Id) Questions
from Posts
where PostTypeid = 1 -- 1 = Question
group by datepart(year, Posts.CreationDate), datepart(month, Posts.CreationDate)
) AS P
left JOIN
(
select
datepart(year, Votes.CreationDate) Year,
datepart(month, Votes.CreationDate) Month,
count(Votes.Id) Bounties,
sum(Votes.BountyAmount) Amount
from Votes
where VoteTypeId = 9 -- 9 = BountyAwarded
group by datepart(year, Votes.CreationDate), datepart(month, Votes.CreationDate)
) AS V
ON P.Year = V.Year AND P.Month = V.Month
order by P.Year, P.Month
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜