CODEWITHSUNDEEP
sqlpostgresqlselectcountleft-join
Ahmet Karakaya
Ahmet Karakaya

Reputation: 10139

SQL Count non existing item

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?

Upvotes: 4

Views: 1351

Answers (4)

xmo
xmo

Reputation: 785

I think a LEFT OUTER JOIN on the results would be a simple and fast solution.

Like other answers, assuming you have a "states" table lists the desired "states", you can join the "status" table with the "group by" results and fill NULL values as 0.

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

And the results would look like this (tested in SQLite):

status            numGroups
---------------------------
OK                2
NOK               1
PENDING           0
REJECTED          0

Note that the COALESCE function maybe called differently in other databases, e.g. IFNULL in SQLite, or ISNULL in SQL server

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

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;

Upvotes: 6

Pawan Lakhara
Pawan Lakhara

Reputation: 1150

Try this

select g.status, ISNULL(COUNT(*),0) from groups g group by g.status;

Upvotes: -1

Joe Taras
Joe Taras

Reputation: 15379

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

Upvotes: 2

Related Questions