CODEWITHSUNDEEP
sql
Ahmet Karakaya
Ahmet Karakaya

Reputation: 10139

SQL Count non existing item with two column

I have asked similar question SQL Count non existing item. But I need another point of view. Here is my different version of question.

I have table Groups,

ID  NUMBER
STATUS  VARCHAR2(20 BYTE)
OWNER  VARCHAR2(20 BYTE)

I am able to count the number of status as following. (taking account owner tuple as well)

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

OWNER                STATUS                  COUNTRS
-------------------- -------------------- ----------
JOHN                 NOK                           1 
JOHN                 OK                            2 
MARK                 OK                            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.

OWNER                STATUS                  COUNTRS
-------------------- -------------------- ----------
JOHN                 NOK                           1 
JOHN                 OK                            2 
JOHN                 PENDING                       0 
JOHN                 REJECTED                      0 
MARK                 OK                            1 
MARK                 NOK                           0
MARK                 PENDING                       0 
MARK                 REJECTED                      0

Upvotes: 0

Views: 110

Answers (2)

athabaska
athabaska

Reputation: 455

Add a "Status" table to your DB. Change "Status" field in "Groups" table to "StatusId", which should link to that new table. Then in your select you can right join "Status" table, and this will give rows in query result for missing status too.

Upvotes: 0

John Woo
John Woo

Reputation: 263703

This query below will get the cartesian product of unique owner on table group to the number of records of status. The result rows will then be joined on table groups so we can be able to count the number of status per owner.

SELECT  a.owner, b.status,
        COUNT(g.status) TotalCount
FROM    (SELECT DISTINCT OWNER FROM groups) a
        CROSS JOIN
        (
            SELECT  'NOK' `status` UNION ALL
            SELECT  'OK' `status` UNION ALL
            SELECT  'PENDING' `status` UNION ALL
            SELECT  'REJECTED' `status` 
        ) b
        LEFT JOIN groups g
            ON  a.owner = g.owner
                AND b.status = g.status
GROUP   BY a.owner, b.status
ORDER   BY a.owner, b.status

Upvotes: 1

Related Questions