Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

combine two queries that count by month

Tags:

sql

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, and 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.

like image 762
Bill the Lizard Avatar asked Jun 25 '26 07:06

Bill the Lizard


1 Answers

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
like image 144
shahkalpesh Avatar answered Jun 27 '26 20:06

shahkalpesh



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!