i'm trying to get an id , and a count in same query result. the problem is when the record doesn't exist , the count return null instead of 0

this is the query

 SELECT DISTINCT Id
,(
    SELECT count(*)
    FROM table1
    WHERE reference_id = 300000009798620
    )
FROM table1
WHERE reference_id = 300000009798620;

CodePudding user response：

Just use:

SELECT max(id) as id, count(*)
FROM table1
WHERE reference_id = 300000009798620

CodePudding user response：

Please try the below modified query

SELECT 300000009798620 as reference_id, count(*)
FROM table1 WHERE reference_id = 300000009798620

CodePudding user response：

Would this do?

• table1 CTE represents your table; I simplified REFERENCE_ID. Both rows share the same reference_id value (1), with two different ID column values (1 and 2)
• temp1 selects IDs for par_reference_id parameter value
• temp2 counts rows for par_reference_id

First execution returns something as par_reference_id = 1 (count is 2):

SQL> with
  2  table1 (id, reference_id) as
  3    -- this represents your TABLE1 (but reference_id is way simpler)
  4    (select 1, 1 from dual union all
  5     select 2, 1 from dual),
  6  temp_1 as
  7    -- distinct IDs per desired reference_id
  8    (select distinct id
  9     from table1
 10     where reference_id = &&par_reference_id
 11    ),
 12  temp_2 as
 13    -- number of rows for that reference_id
 14    (select count(*) cnt
 15     from table1
 16     where reference_id = &&par_reference_id
 17    )
 18  -- and finally:
 19  select b.id, a.cnt
 20  from temp_2 a left join temp_1 b on 1 = 1;
Enter value for par_reference_id: 1

        ID        CNT
---------- ----------
         1          2
         2          2

Let's try some other reference_id value (which doesn't exist in table1), e.g. 100: query doesn't return any ID, but count = 0 (as you wanted):

SQL> undefine par_reference_id
SQL> /
Enter value for par_reference_id: 100

        ID        CNT
---------- ----------
                    0

SQL>

CodePudding user response：

You can use DUAL to always get a row back with your ID, then a subquery to get the count.

SELECT 300000009798620                            AS id,
       (SELECT COUNT (*)
          FROM table1
         WHERE reference_id = 300000009798620)    AS amt
  FROM DUAL;