x
CREATE TABLE test_table (
e_id NUMBER(10),
e_value NUMBER(10)
);
INSERT INTO test_table VALUES(11,123);
INSERT INTO test_table VALUES(11,123);
INSERT INTO test_table VALUES(11,null);
COMMIT;
I want to fetch single record from the table test_table
if any one of the value is null
in the column e_value
then it should print No
else Yes
My Attempt:
SELECT e_id,
CASE
WHEN e_value IS NULL THEN 'No'
ELSE 'Yes'
END is_answer
FROM test_table;
Current Output:
+------+-----------+
| e_id | is_answer |
+------+-----------+
| 11 | Yes |
| 11 | Yes |
| 11 | No |
+------+-----------+
Expected Output:
+------+-----------+
| e_id | is_answer |
+------+-----------+
| 11 | No |
+------+-----------+
Advertisement
Answer
you can use GROUP BY
and COUNT
:
SELECT e_id,
CASE
WHEN count(e_id) = count(e_value) THEN 'Yes'
ELSE 'No'
END is_answer
FROM test_table
GROUP BY e_id;
The count will return number of non-null values
db<>fiddle here