-
-
Save hengkiardo/d99c4cdde0032cf753668c5da7f01a66 to your computer and use it in GitHub Desktop.
gl
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment