PostgreSQL , CASE WHEN

Question

i have query

select case
  when (status = 'MENANG' is null ) then '0'
  when (status = 'KALAH' is null) then '0'
  when (status = 'PROSES' is null) then '0'
  when (status = 'BATAL' is null) then '0'
  when (status = 'MUNDUR' is null) then '0'
  else status 
  end as data_status,
  count(a.kode_barang) as total 
  from tbl barang 
  group by status

here I have a query and some status, here I use the count to calculate the number of items based on status

when run,

data_status | jumlah (count)
KALAH              2
kosong             4
MENANG             2
PROSES             11

how to display all status data, including status data whose value is 0 or null

Do you have a table that contains all the possible values for status? — user330315
– user330315, Commented May 31, 2021 at 8:10
the table above with the status 'MUNDUR' does not exist, therefore I want to display the status 'MUNDUR' = 0 — Jon
– Jon, Commented May 31, 2021 at 8:11

user330315 · Accepted Answer · 2021-05-31 08:13:29Z

1

If you don't have a table that contains all possible values for status you can use a VALUES list and a LEFT JOIN:

select v.status, 
       count(b.status) as total
from ( 
      values ('MENANG', 'KALAH', 'PROSES', 'BATAL', 'MUNDUR')
  ) as v(status)
  left join barang b on b.status = v.status
group by v.status

answered May 31, 2021 at 8:13

user330315

Sign up to request clarification or add additional context in comments.

Comments

Ftisiot · Accepted Answer · 2021-05-31 08:21:49Z

If you have the list of possible statuses available in a table you just need to join with a LEFT OUTER JOIN

If your list_of_status table is is:

create table list_of_status (status varchar);
insert into  list_of_status values('MENANG');
insert into  list_of_status values('KALAH');
insert into  list_of_status values('PROSES');
insert into  list_of_status values('BATAL');
insert into  list_of_status values('MUNDUR');

and your data_status is

create table data_status (status varchar,kode_barang int);
insert into data_status values ('MENANG',3);
insert into data_status values ('KALAH',2);
insert into data_status values ('PROSES',3);
insert into data_status values ('MENANG',3);

SELECT 
list_of_status.STATUS,
count(kode_barang) as total 
FROM list_of_status LEFT OUTER JOIN data_status ON list_of_status.STATUS= data_status.STATUS
group by list_of_status.STATUS

with the expected result of

 status | total 
--------+-------
 KALAH  |     1
 BATAL  |     0
 MENANG |     2
 PROSES |     1
 MUNDUR |     0
(5 rows)

If you want all the statuses in th list_of_status table PLUS any other status present in the data_status you can achieve it with a FULL_OUTER_JOIN.

e.g. if we include in the data_status a row for TEST status not available in list_of_status with

insert into data_status values ('TEST',5);

The following query returns the desired result including the TEST row

select
COALESCE(list_of_status.STATUS, data_status.STATUS) STATUS,
count(kode_barang) as total 
FROM list_of_status FULL OUTER JOIN data_status ON list_of_status.STATUS= data_status.STATUS
group by COALESCE(list_of_status.STATUS, data_status.STATUS)

Result:

 status | total 
--------+-------
 TEST   |     1
 MUNDUR |     0
 MENANG |     2
 PROSES |     1
 KALAH  |     1
 BATAL  |     0
(6 rows)

Collectives™ on Stack Overflow

PostgreSQL , CASE WHEN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related