CODEWITHSUNDEEP
sql
Niclas
Niclas

Reputation: 1406

Merging count(*) from different tables with different WHERE condition

I have three tables

OWNERS: pk name
POSTS: pk, post_date, owner_pk
REPLIES: pk,reply_date, post_pk,owner_pk,comment_count

Between two dates: date1 and date2 I want a result as:

OWNER:name COUNT(posts) SUM(comment_count)

My problem is that I can't understand how to write a single query that sums the POSTS REPLIES tables separatly.

Ex: Day1 a post is done with 2 comments. Day2 a post is done with no comments. The reply table stores the total number of comments for a given post/day.

PostA     Day1
CommentA1 Day1 
CommentA2 Day1 
PostB     Day2
CommentA3 Day2
PostC     Day3

Running the query for Day1:

Owner:Name count(post) sum(comment_count)
OwnerName  1           2

Running the query for Day2:

OwnerName  1         1

Running the query for Day3:

OwnerName  1         0

Upvotes: 0

Views: 91

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Instead of a SUM(), what about a COUNT(DISTINCT)?

SELECT
    o.name,
    COUNT(DISTINCT p.pk) AS posts,
    COUNT(DISTINCT r.pk) AS replies
FROM
    owners o
    LEFT JOIN posts p ON p.owner_pk = o.pk AND p.post_date BETWEEN @date1 AND @date2
    LEFT JOIN replies r ON r.post_pk = p.pk AND r.post_date BETWEEN @date1 AND @date2
GROUP BY
    o.pk,
    o.name

Upvotes: 4

RET
RET

Reputation: 9188

You might need to provide a bit more detail in your question, but I suspect you're after something like this:

SELECT name, COUNT(posts.pk), COUNT(replies.pk)
  FROM owners, OUTER (posts, OUTER replies)
  WHERE owners.pk = posts.owner_pk
    AND posts.pk = replies.post_pk
    AND (posts.post_date BETWEEN ? AND ? OR replies.reply_date BETWEEN ? AND ?)
  GROUP BY name

Upvotes: 1

Related Questions