How to check existence in Oracle using a condition?-CodePudding

I'm trying to check if a column value exists using this query that actualy works fine

SELECT   COUNT(1)
FROM my_Table
WHERE mycolumn1 = 'BJS'
AND mycolumn2 = '100000'

The result is 0 that means mycolumn2 ' s value does not exists , if The result is 1 that means it does exists

Now , i'm trying to add mycolumn2 to be displayed using this

SELECT   COUNT(1) , mycolumn2  
FROM my_Table
WHERE mycolumn1 = 'BJS'
AND mycolumn2 = '100000'
group by mycolumn2

But , it does not work and it displays nothing

What i'm missing ?

CodePudding user response：

You need to make sure you generate at least one row for each group:

SELECT d.mycolumn2,
       COUNT(m.mycolumn2)
FROM   (SELECT '100000' AS mycolumn2 FROM DUAL) d
       LEFT OUTER JOIN my_table m
       ON (   m.mycolumn1 = 'BJS'
          AND m.mycolumn2 = d.mycolumn2)
GROUP BY d.mycolumn2

Which, for the sample data:

CREATE TABLE my_table (mycolumn1 VARCHAR2(20), mycolumn2 VARCHAR2(20));

Outputs:

MYCOLUMN2	COUNT(M.MYCOLUMN2)
100000	0

fiddle

CodePudding user response：

It is because of the GROUP-BY statement in the second query.

In the case that the table does not have any rows that match the WHERE condition (mycolumn1 = 'BJS' AND mycolumn2 = '100000'), there would be no groups produced by the GROUP-BY statement.

CodePudding user response：

Use an outer join so the existence of the row is optional, and count one of the columns from the joined table so you get an accurate count:

SELECT COUNT(my.mycolumn1),
       mt.mycolumn2  
FROM DUAL
LEFT OUTER JOIN my_Table mt
  ON mt.mycolumn1 = 'BJS' AND
     mt.mycolumn2 = '100000'
group by mt.mycolumn2

db<>fiddle here