mysql - (mysql) 子查询返回多于一行

Question

我正在尝试按升序获取每个区(“kecamatan”)中“jml_pddk_miskin”的总数。但我收到错误“子查询返回多于一行”。

这是我的查询:

select 
mkec.id AS id,mkec.nama AS kecamatan,rpb.tahun AS tahun,
ifnull((select sum(rpb.jml_pddk_miskin) 
        from rekap_penduduk_bps rpb 
        join master_kelurahan mkel on mkel.id = rpb.id_kelurahan 
        where 
        mkel.id_kecamatan = mkec.id 
        and rpb.id_kelurahan = mkel.id 
        group by rpb.id ),0) AS total_miskin 
from master_kecamatan mkec 
join rekap_penduduk_bps rpb 
group by mkec.nama 
order by mkec.id

这是上述查询的结果: result query

我想要这样的结果... result query

Answer 1

试试这个，

   SELECT mkec.id AS id,mkec.nama AS kecamatan,rpb.tahun AS tahun,IFNULL((SELECT SUM(rpb.jml_pddk_miskin) 
FROM (rekap_penduduk_bps rpb JOIN master_kelurahan mkel ON mkel.id = rpb.id_kelurahan) WHERE 
mkel.id_kecamatan = mkec.id AND rpb.id_kelurahan = mkel.id GROUP BY rpb.id HAVING rpb.id_kelurahan = mkel.id ),0)
 AS total_miskin FROM master_kecamatan mkec JOIN rekap_penduduk_bps rpb ON mkec.id= rpb.id_kelurahan GROUP BY mkec.nama ORDER BY mkec.id