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.
id asrama_id period_id 1 1 1
id name 1 A 2 B
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
Basically, the condition needs to be in the
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
0in 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
idis a primary/unique key. However, it is a good habit if you are learning SQL.