gl

sql

SELECT  
p.id as "Pool Id",
p.status,
p.province_id,
 CASE
	WHEN province_id = 11 THEN "Aceh"
	WHEN province_id = 12 THEN "Sumatera Utara"
	WHEN province_id = 13 THEN "Sumatera Barat"
	WHEN province_id = 14 THEN "Riau"
	WHEN province_id = 15 THEN "Jambi"
	WHEN province_id = 16 THEN "Sumatera Selatan"
	WHEN province_id = 17 THEN "Bengkulu"
	WHEN province_id = 18 THEN "Lampung"
	WHEN province_id = 19 THEN "Kepulauan Bangka Belitung"
	WHEN province_id = 21 THEN "Kepulauan Riau"
	WHEN province_id = 31 THEN "DKI Jakarta"
	WHEN province_id = 32 THEN "Jawa Barat"
	WHEN province_id = 33 THEN "Jawa Tengah"
	WHEN province_id = 34 THEN "DI Yogyakarta"
	WHEN province_id = 35 THEN "Jawa Timur"
	WHEN province_id = 36 THEN "Banten"
	WHEN province_id = 51 THEN "Bali"
	WHEN province_id = 52 THEN "Nusa Tenggara Barat"
	WHEN province_id = 53 THEN "Nusa Tenggara Timur"
	WHEN province_id = 61 THEN "Kalimantan Barat"
	WHEN province_id = 62 THEN "Kalimantan Tengah"
	WHEN province_id = 63 THEN "Kalimantan Selatan"
	WHEN province_id = 64 THEN "Kalimantan Timur"
	WHEN province_id = 65 THEN "Kalimantan Utara"
	WHEN province_id = 71 THEN "Sulawesi Utara"
	WHEN province_id = 72 THEN "Sulawesi Tengah"
	WHEN province_id = 73 THEN "Sulawesi Selatan"
	WHEN province_id = 74 THEN "Sulawesi Tenggara"
	WHEN province_id = 75 THEN "Gorontalo"
	WHEN province_id = 76 THEN "Sulawesi Barat"
	WHEN province_id = 81 THEN "Maluku"
	WHEN province_id = 82 THEN "Maluku Utara"
	WHEN province_id = 91 THEN "Papua Barat"
	WHEN province_id = 94 THEN "Papua"
END as Provinsi
, joinPool.total as "Total Peserta"
, p.capacity
, resultBatch.totalRs as "Total Penerima"
FROM batch.pool p 
LEFT JOIN 
    (SELECT join_pool.id, count(1) as total,
     pool_id
     FROM batch.join_pool
     -- where status = 1
    GROUP BY batch.join_pool.pool_id) as joinPool
ON p.id=joinPool.pool_id
LEFT JOIN 
    (SELECT count(1) as totalRs, pool_id
     FROM result as rs
     JOIN join_pool as jp
     ON jp.id = rs.join_pool_id
     where rs.status = 1
    GROUP BY jp.pool_id) as resultBatch
ON p.id=resultBatch.pool_id
where p.id != 19
ORDER BY 5 desc