how to select in select postgresql

Question

I have a column named jenis_kelamin I want to display the gender of male and female in a select query. I have a query like this but the data that comes out is still wrong, what is the correct way?

SELECT distinct mj.id, mj.nama_pd,
(select distinct count(jenis_layanan) from public.isian_kuis 
 where jenis_kelamin = 'Laki' ) as jumlah_laki,
 (select distinct count(jenis_layanan)
 from public.isian_kuis
 where jenis_kelamin = 'Perempuan' ) as jumlah_perempuan
 FROM public.master_jabatan mj
 join isian_kuis ik on ik.jabatan_id = mj.id
  group by mj.id,mj.nama_pd
   order by mj.id;

I have an example of the image of my query, this is still wrong

The correct data is that at ID 30 it has two men and one woman, in ID 29 there is only one woman

Answer 1

In Postgres, you can use conditional aggregation which looks like:

SELECT mj.id, mj.nama_pd, 
       COUNT(*) FILTER (WHERE jenis_kelamin = 'Laki')AS jumlah_laki,
       COUNT(*) FILTER (WHERE jenis_kelamin = 'Perempuan')  AS jumlah_perempuan
FROM public.master_jabatan mj JOIN
     isian_kuis ik 
     ON ik.jabatan_id = mj.id
GROUP BY mj.id, mj.nama_pd
ORDER BY mj.id;

Note that it is very, very rare to use SELECT DISTINCT with GROUP BY. This might slow down the query. And FILTER is standard SQL so is a good way to implement this.

Answer 2

No need to use nested select just use Group By like this:

SELECT distinct mj.id, 
  mj.nama_pd, 
  SUM(CASE WHEN jenis_kelamin = 'Laki' THEN 1 ELSE 0 end) AS jumlah_laki,
  SUM(CASE WHEN jenis_kelamin = 'Perempuan' THEN 1 ELSE 0 end) AS jumlah_perempuan
FROM public.master_jabatan mj
join isian_kuis ik on ik.jabatan_id = mj.id
group by mj.id,mj.nama_pd
order by mj.id;