CODEWITHSUNDEEP
sqlpostgresqljoincountleft-join
Dan
Dan

Reputation: 2837

Postgres LEFT JOIN and COUNT

I'm having a bit of trouble debugging a SQL query and would really appreciate some help.

Here is the query:

SELECT p.id, p.type, p.submission_id, 
  p.title, p.description, p.date, extract('epoch' FROM p.time) AS time, 
  podcasts.image_url, podcasts.title AS podcast_title, 
  COUNT(u1) as upvote_count, u2.id as upvote_id, 
  episodes.mp3_url, episodes.duration, 
  COUNT(c) as comment_count 
FROM posts AS p LEFT JOIN upvotes AS u1 ON p.id=u1.post_id AND u1.comment_id=-1 
LEFT JOIN upvotes AS u2 ON p.id=u2.post_id AND u2.user_id=$1 AND u2.comment_id=-1 
LEFT JOIN episodes ON p.submission_id = episodes.id 
LEFT JOIN podcasts ON episodes.podcast_id=podcasts.id 
LEFT JOIN comments AS c ON c.post_id=p.id 
WHERE p.type='podcast' AND p.time IS NOT NULL 
GROUP BY(p.id, u2.id, podcasts.image_url, episodes.mp3_url, episodes.duration, podcasts.title);

The unexpected behavior comes from the two COUNT statements. I expect upvote_count to be equivalent to 

SELECT COUNT(*) FROM upvotes WHERE upvotes.post_id = (individual post id);

for each individual post and same for comment count (which I expect to return the total number of comments for each post. However, I am getting strange seemingly random results from these queries for those two fields. Can anybody help me diagnose the problem?

Upvotes: 2

Views: 10747

Answers (1)

user330315
user330315

Reputation:

count() (and all other aggregate functions) ignores null values.

However, COUNT(c) references the complete row ("record") from the table alias c But that is is always not null even when all columns of that record are null.

You need to change both count() calls and pass a column from that table to it, e.g. count(u1.post_id) and count(c.post_id)

Upvotes: 2

Related Questions