Oracle | If row not found then output count 0

Given a list of client names (comma separated) I need to find out how many exists in Client table and how many dont. The table and input lists are both huge; here I’m just giving table as an example of my requirement.

Client

client_name	status
abc	1
def	1
ghi	0
jkl	1

Query I thought of using is

select client_name, count(client_name) over (partition by client_name) count_client from client where status = 1 and client_name in ('abc', 'xyz', 'ghi', 'jkl');

This returns:

client_name	count_client
abc	1
jkl	1

But what I need is

client_name	count_client
abc	1
xyz	0
ghi	0
jkl	1

Could someone please help me with the SQL query

Answer

If you have “huge” amount of data, then your best option is to store list of values you’re interested in into a separate table. That’s trivial.

Otherwise, as an alternative you could try something like this (sample data in lines #1 – 11; query begins at line #12):

SQL> WITH
  2     client (client_name, status)
  3     AS
  4        -- this is contents of your table
  5        (SELECT 'abc', 1 FROM DUAL
  6         UNION ALL
  7         SELECT 'def', 1 FROM DUAL
  8         UNION ALL
  9         SELECT 'ghi', 0 FROM DUAL
 10         UNION ALL
 11         SELECT 'jkl', 1 FROM DUAL)
 12    -- join your table with a collection
 13    SELECT t.COLUMN_VALUE AS client_name, NVL (SUM (c.status), 0) AS count_client
 14      FROM client c
 15           RIGHT JOIN TABLE (sys.odcivarchar2list ('abc',
 16                                                   'xyz',
 17                                                   'ghi',
 18                                                   'jkl')) t
 19              ON     t.COLUMN_VALUE = c.client_name
 20                 AND c.status = 1
 21  GROUP BY t.COLUMN_VALUE
 22  ORDER BY t.COLUMN_VALUE;

CLIENT_NAME     COUNT_CLIENT
--------------- ------------
abc                        1
ghi                        0
jkl                        1
xyz                        0

SQL>