SQL Count non existing item

Question

I have table Groups,

ID  NUMBER
STATUS  VARCHAR2(20 BYTE)

I am able to count the number of status as following.

select g.status, count(*) from groups g group by g.status;

STATUS                 COUNT(*)
-------------------- ----------
OK                            2 
NOK                           1 

I have another status ,say PENDING, REJECTED. But there is no item exists in table, but I want them to be shown with zero count as following.

STATUS                 COUNT(*)
-------------------- ----------
OK                            2 
NOK                           1 
PENDING                       0 
REJECTED                      0 

What will be the SQL statement to make it possible?

Answer 1

Try this

SELECT A.status, COUNT(DISTINCT G.ID) statusCnt
FROM (SELECT 'OK' status FROM DUAL
      UNION 
      SELECT 'NOK' status FROM DUAL
      UNION 
      SELECT 'PENDING' status FROM DUAL
      UNION 
      SELECT 'REJECTED' status FROM DUAL
     ) AS A 
LEFT JOIN groups G ON A.status = G.STATUS 
GROUP BY A.status;

Answer 2

If exists a table with list of states you can write your query in this way:

I suppose your state registry has called STATES

SELECT
    states.status,
    (select count(*) from groups g where g.status = states.status)
FROM states

Alternatively:

SELECT
    s.status, count(*)
FROM states s
LEFT OUTER JOIN groups g
    ON s.status = g.status
GROUP BY s.status

Otherwise you can't obtain this information

EDIT (AFTER COMMENT)

Please create a table:

CREATE TABLE states
(id int,
status varchar(20))

In your tables GROUPS replace status field with fk to states table