TSQL Counting Nulls - where all grouped items have NULL field

Question

Just trying to wrap my head around the logic/commands needed:

I have a contacts table. Each contact has a client in a many-to-one fashion. I am trying to get any clientIDs where all contacts have a NULL value for email.

Example data:

ContactID   EmailAddress    ClientID
1           NULL                3
907         NULL                3
2468        NULL                3
2469        [email protected]     4
1077        NULL                4
908         [email protected]     4
2           [email protected]     4
3           [email protected]     5
909         [email protected]     5

Thanks!

related question - http://stackoverflow.com/questions/3523036/what-is-the-oracle-equivalent-of-sql-servers-isnull-function — Erran Morad, Apr 03 '14 at 05:35

StuartLC · Accepted Answer · 2014-04-03T05:49:58.323

7

You can do this with a grouped aggregate and a HAVING clause (and assuming blank email addresses can be treated the same as nulls):

SELECT 
    ClientID
    FROM ClientEmails
    GROUP BY ClientID
    HAVING MAX(LEN(ISNULL(EmailAddress, ''))) = 0;

SqlFiddle

edited Apr 03 '14 at 05:49

answered Apr 03 '14 at 05:28

StuartLC

104,537
17
209
285

Nice. Is this database agnostic ? Also, can you tell me if my query is okay too. thank you. chenqui. – Erran Morad Apr 03 '14 at 05:32
2

HAVING is ANSI. `ISNULL` and `LEN` aren't - `COALESCE` would be more portable than `ISNULL` (and other alternatives exist, e.g. Oracle has NVL). Most DB's have equivalents to `LEN`, e.g. `LENGTH `. Your query is fine - `COUNT(x)` excluding NULLs seems to be [standard behaviour](http://my.safaribooksonline.com/book/databases/sql/0596004818/4dot2dot-ansi-sql-aggregate-functions/id3752538) – StuartLC Apr 03 '14 at 05:44

score 7 · Answer 2 · answered Apr 03 '14 at 05:28

7

Would this help ?

select *
from
(
select clientId, 
COUNT(emailAddress) as Mailz, 
COUNT(contactId) as Contacts
from contacts
group by clientId
) as src
where (Mailz = 0 and contacts > 0)

answered Apr 03 '14 at 05:28

Erran Morad

4,563
10
43
72

Can you also elaborate? – potashin May 01 '15 at 03:11

TechDo · Answer 3 · 2014-04-03T05:35:20.233

2

Please try:

SELECT 
    ClientID
FROM(
    SELECT 
        ClientID, 
        SUM(CASE WHEN EmailAddress IS NULL THEN 1 ELSE 0 END) a,
        COUNT(*) b
    FROM YourTable
    GROUP BY ClientID
)x WHERE a=b

edited Apr 03 '14 at 05:35

answered Apr 03 '14 at 05:13

TechDo

18,398
3
51
64

1

can be done without having a subquery, just filter it in `HAVING` clause eg. `HAVING COUNT(*) = SUM(CASE WHEN EmailAddress IS NULL THEN 1 ELSE 0 END)` – John Woo Apr 03 '14 at 05:42

TSQL Counting Nulls - where all grouped items have NULL field

3 Answers3