CODEWITHSUNDEEP
mysqlsqlhiveql
nOObda
nOObda

Reputation: 123

Selecting distinct count in a group with only null values for a specific column

I have 2 columns like this - id and val.

enter image description here

I require such distinct id's where corresponding to each id there is a null value present. Is it plausible to use "group by" by id and then use "having" clause where null is there?

Upvotes: 0

Views: 1278

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use NOT EXISTS :

SELECT COUNT(DISTINCT id)
FROM table t
WHERE NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.id = t.id AND t1.val IS NOT NULL);

Other option uses the GROUP BY :

SELECT COUNT(id)
FROM table t
GROUP BY id
HAVING SUM(CASE WHEN val IS NOT NULL THEN 1 ELSE 0 END) = 0;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

To get ids that have a NULL value, I would be inclined to start with this:

select id
from t
group by id
having count(*) <> count(val);

This structure allows you to check for other values, such as a non-NULL value.

The simplest method to get the distinct ids with NULL values is:

select distinct id
from t
where val is null;

If you only want the count:

select count(distinct id)
from t
where val is null;

Upvotes: 1

Related Questions