CODEWITHSUNDEEP
mysqlsql
hugo
hugo

Reputation: 1245

Multiple column counts by week

I have the following two tables:

Posts

Comments

I want to create a report that shows the weekly post count and comment count. Something like this:

Week    StartDate      Posts     Comments
1       1/1/2012       100        305
2       1/8/2012       115        412

I have this query but it only pulls form the Posts table. 

select makedate( left(yearweek(p.post_timestamp),1),week(p.post_timestamp, 2 ) * 7 ) as Week, COUNT(p.post_id) as Posts  
FROM cl_posts p
GROUP BY Week
ORDER BY WEEK(p.post_timestamp)

How do I add the Comment count too?

Upvotes: 1

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Here is one way, using join:

select coalesce(p.week, c.week) as week, p.Posts, c.Comments
from (select makedate( left(yearweek(p.post_timestamp),1),week(p.post_timestamp, 2 ) * 7 ) as Week,   
             COUNT(*) as Posts  
      FROM cl_posts p
      GROUP BY Week
     ) p full outer join
     (select makedate( left(yearweek(c.comment_timestamp),1),week(c.comment_timestamp, 2 ) * 7 ) as Week,   
             COUNT(*) as Comments
      FROM cl_comments c
      GROUP BY Week
     ) c
     on p.week = c.week
order by 1

The reason that I'm using a full outer join instead of another join type is to keep weeks even when one or the other counts are 0. The reason I'm not joining the tables together is because, presumably, you want the report by the comment date, not the post date of the post associated with the comment.

Upvotes: 0

fthiella
fthiella

Reputation: 49049

I think you need something like this:

select
  week(post_timestamp) as Week,
  adddate(date(post_timestamp), INTERVAL 1-DAYOFWEEK(post_timestamp) DAY) as StartDate,
  count(distinct post_id),
  count(comment_id)
from
  posts left join comments
  on comments.posts_post_id = posts.post_id
group by Week, StartDate

Upvotes: 3

Related Questions