how to make count query and join two table

Question

i have two table alamat_penerima and lokasi

alamat penerima table [this is my record

and then

lokasi table [this is the record

i wont to make two table like this [view]

I hope you can help me

i have just try this query command :

            SELECT COUNT(alamat_penerima.`nama_penerima`) AS penerima, lokasi.`wilayah`
        FROM lokasi
        INNER JOIN alamat_penerima ON alamat_penerima.`kota_kab` = lokasi.`wilayah`
        GROUP BY alamat_penerima.`kota_kab`

but the result is

result

Answer 1

Your question is difficult to understand, so this answer is guesswork. It looks to me like you hope to list the number of recipient addresses in each region, but also show the regions with no addresses.

To do that, you need two subqueries joined together.

This subquery generates the names of all regions.

                 SELECT DISTINCT wilayah FROM lokasi

This subquery generates a list of regions with the count of addresses in each region. The result set from this subquery, however, omits regions with no addresses in them.

                SELECT COUNT(*) num, kota_kab AS wilayah
                  FROM alamat_penerima
                 GROUP BY kota_kab

You can test these subqueries individually to determine whether they are correct. It's important to do that.

Finally, you join these two together as if they were tables. (That's why it's called structured query language).

SELECT w.wilayah, a.num
  FROM (
                 SELECT DISTINCT wilayah FROM lokasi
       ) w
  LEFT JOIN (
                SELECT COUNT(*) num, kota_kab AS wilayah
                  FROM alamat_penerima
                 GROUP BY kota_kab
       ) a ON w.wilaya = a.wilaya

This will yield what you want, but showing NULL instead of 0 in rows with no addresses. (That's a result of using LEFT JOIN) You can put the 0 values there by using this as the first line of your query instead.

SELECT w.wilayah, IFNULL(a.num,0) num

The design trick is to make your first subquery determine the number of rows in your result set, then to use LEFT JOIN to put information from subsequent subqueries into your results.