Rows with 0 value won't appear on Count in SQL query

Question

I have to get all the posts even if they have 0 likes on the count, but those aren't showing up no matter what, I tried inner join, left outer join and nothing

select 
    post.id as idPost, 
    post.data, 
    autor.nome as nome, 
    post.texto, 
    post.idAutor, 
    count(likes.idAutor) as numeroLikes 
from post  
join utilizador autor 
    on autor.id = post.idAutor 
left join likes 
    on post.id = likes.idPost 
left join amigos as a 
    on a.idAmigo2 = post.idAutor   
where (post.idAutor = a.idAmigo2 and a.idAmigo1 = 3) or post.idAutor = 3 
    and idPost > 0 
group by post.id~

This is posts table, you can see it's up to ID 43

post table

and this is the result from the query i posted, you can see it doesn't show the number of likes for idPost 43 because it's 0

enter image description here

Perhaps this thread will help you https://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate — DanielY, Jul 19 '17 at 04:52
Your query has many problems. Please edit the question and show us sample data along with your desired output. — Tim Biegeleisen, Jul 19 '17 at 05:08
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , Jul 19 '17 at 05:54

score 2 · Accepted Answer · answered Jul 19 '17 at 05:23

2

Your left outer join on likes should work when you avoid value comparions attributes of likes in the where clause. The comparison forces all rows to have non null values for likesin the result. You should modify idPost > 0 either to (idPost is null or idPost > 0) or move this comparison into the approriate join clause.

answered Jul 19 '17 at 05:23

clemens

16,716
11
50
65

thank you, this worked (idPost is null or idPost > 0) – ruipascoal Jul 19 '17 at 05:30
Do you have values for `idPost` less or equal to 0? If not you can completly remove this comparison. – clemens Jul 19 '17 at 05:33
I can't because it is needed to stop comments from being refreshes everytime on website and only refresh the new ones as they are created – ruipascoal Jul 19 '17 at 05:34

Tim Biegeleisen · Answer 2 · 2017-07-19T05:29:25.113

Here is a query which should not suffer from the problem of posts disappearing because they have no likes:

SELECT 
    post.id as idPost,
COUNT(likes.idAutor) AS numeroLikes 
FROM post  
LEFT JOIN utilizador autor 
    ON autor.id = post.idAutor 
LEFT JOIN likes 
    ON post.id = likes.idPost 
LEFT JOIN amigos AS a 
    ON a.idAmigo2 = post.idAutor AND 
       ((post.idAutor = a.idAmigo2 AND a.idAmigo1 = 3) OR
      post.idAutor = 3)
WHERE idPost > 0 
GROUP BY post.id

I made three major changes to your query:

LEFT JOIN from post to utilizador; this alone might already fix the problem
Move all conditions from the WHERE clause into an ON clause, which should prevent posts from being dropped.
Note also that I only select the post ID and the count. The reason for this is that your current select clause is invalid as it contains non aggregate columns.

Rows with 0 value won't appear on Count in SQL query

2 Answers2