MySQL include zero rows when using COUNT with GROUP BY

lets say you have, 3 table:

1. store table

id_toko   kode_toko  kode_kota
6         SBY006     2
7         JKT007     5
8         BGR008     7
20        JKT002     5

2. city table

id_kota  nama_kota
2        Surabaya
5 	 Jakarta
7 	 Bogor
9 	 Bandung

3. sale table

id_sinc  id_toko
1        JKT002
3        JKT002
5        JKT002

and i want this result

kota      sale
Surabaya  0
Jakarta   3
Bogor     0
Bandung   0

here is the query

select k.nama_kota as kota, coalesce(j.jml, 0) as jml

from tbl_kota k

left join (
   SELECT b.id_kota as kota,count(a.id_sinc) jml FROM `tbl_syncronasi` a

   left join tbl_toko c on a.id_toko = c.kode_toko
   left join tbl_kota b on c.kode_kota = b.id_kota 
   where id_status=1

   group by b.nama_kota order by b.nama_kota

) j on

j.kota = k.id_kota

Published by

G3n1k

just to remember what i had known :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s