SQL count not showing 0 result

I have 2 table member_asrama and asrama. I want count row row_asrama with condition, but the result not showing result 0 from count.

Table member_asrama:

id    asrama_id    period_id    
1     1            1

Table asrama

id    name       
1     A
2     B

My query

SELECT asrama.id,asrama.name, COUNT(*) as cnt
FROM asrama
left join member_asrama
on asrama.id = member_asrama.`asrama_id` 
where member_asrama.`period_id` = 1
group by asrama.id


asrama.id asrama.name cnt
1          A           1

I want result

asrama.id asrama.name cnt
1          A           1
2          B           0

1 answer

  • answered 2020-03-25 13:38 Gordon Linoff

    Basically, the condition needs to be in the ON clause:

    select a.id, a.name, count(ma.asrama_id) as cnt
    from asrama a left join
         member_asrama ma
         on a.id = ma.asrama_id and
            ma.period_id = 1
    group by a.id, a.name;

    Note other changes:

    • COUNT() counts a column from member_asrama. That allows 0 in the results.
    • Table aliases make the query easier to write and to read.
    • Backticks make the query harder to write and read -- and they are not necessary.
    • I included both columns in the GROUP BY. Technically, this is not necessary if id is a primary/unique key. However, it is a good habit if you are learning SQL.