php - Query MySQL To Show Counting from the table relation -
i have 3 tables. 2 reference table , 1 table transactions.
- table puskesmas
mysql> select id,nama puskesmas;
+----+----------------------+ | id | nama | +----+----------------------+ | 1 | bambanglipuro | | 2 | banguntapan | | 3 | bantul | | 4 | dlingo | | 5 | imogiri | | 6 | jetis | | 7 | kasihan | | 8 | kretek | | 9 | pajangan | | 10 | pandak | +----+----------------------+ - table poli
mysql> select * poli;
+----+---------------+ | id | nama | +----+---------------+ | 1 | bp | | 2 | kia | | 3 | gigi | | 4 | jiwa | +----+---------------+ - table loket
mysql> select pasien_id,poli_id,puskesmas_id loket limit 10;
+-----------+---------+--------------+ | pasien_id | poli_id | puskesmas_id | +-----------+---------+--------------+ | 1 | 1 | 1 | | 5 | 2 | 2 | | 1 | 1 | 3 | | 9 | 3 | 4 | | 1 | 3 | 5 | | 5 | 2 | 1 | | 1 | 1 | 3 | | 8 | 1 | 2 | | 10 | 3 | 6 | | 5 | 2 | 5 | +-----------+---------+--------------+ i want show total number (count) of visits puskesmas table. made field all_total fter want take patient visits poli_id = 1 , used column bp. other poly same the
+----+----------------------+--------------+------+------+------+------+ | id | nama | total | bp | kia | gigi | jiwa | +----+----------------------+--------------+------+------+------+------+ | 1 | bambanglipuro | 30 | 10 | 3 | 15 | 2 | | 2 | banguntapan | 35 | 20 | 4 | 11 | 0 | | 3 | bantul | 15 | 10 | 0 | 5 | 0 | | 4 | dlingo | 12 | 10 | 1 | 1 | 0 | | 5 | imogiri | 10 | 5 | 2 | 1 | 2 | | 6 | jetis | 25 | 13 | 3 | 7 | 2 | | 7 | kasihan | 20 | 10 | 10 | 0 | 0 | | 8 | kretek | 23 | 20 | 1 | 1 | 1 | | 9 | pajangan | 10 | 5 | 1 | 3 | 1 | | 10 | pandak | 0 | 0 | 0 | 0 | 0 | +----+----------------------+--------------+------+------+------+------+ i've tried make query display fails hold. can make such queries desired results
i have tried query : select puskesmas_id, sum(case when poli_id=1 1 else 0 end) bp, sum(case when poli_id=5 0 else 1 end) gigi, count(id) total loket group puskesmas_id
and result :
+--------------+------+------+-------+ | puskesmas_id | bp | gigi | total | +--------------+------+------+-------+ | 1 | 97 | 126 | 143 | | 6 | 74 | 185 | 210 | | 8 | 131 | 179 | 190 | | 7 | 90 | 92 | 98 | | 2 | 33 | 39 | 54 | | 4 | 158 | 248 | 263 | | 3 | 66 | 68 | 72 | +--------------+------+------+-------+ but puskesmas_id value 0 not perform data when querying
Comments
Post a Comment