MySQL COUNT with empty column

Question

I have a table tb1 like this:

id  | building  | id_connect |  
====+==========+============
1 |     1    |       "328abbc"    
2 |     3    |       "328abbc"    
3 |     4    |       "212a3b"    
4 |     1    |       "212a3b"  
5 |     2    |       ""  
6 |     2    |       ""
7 |     7    |       ""

I need to get the count of the distinct id_connect if the string is different to "", but each empty string ("") has to add 1...

In the example, the required result would be 5 (one "328abbc", one "212a3b" and the three ""), but I only obtain 3 with

SELECT COUNT(DISTINCT(id_connect))
FROM tb1

eggyal · Accepted Answer · 2012-05-02 10:49:17Z

3

SELECT
    COUNT(CASE WHEN id_connect='' THEN 1 END)
  + COUNT(DISTINCT NULLIF(id_connect, ''))
FROM tb1

answered May 2, 2012 at 10:49

eggyal

127k18 gold badges221 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eggyal Over a year ago

See it running as an alternative on @Bohemian's sqlfiddle here.

Bohemian Over a year ago

@eggyal you're a noob: COUNT(CASE WHEN id_connect='' THEN 1 END) is identical to COUNT(id_connect='')

Bohemian Over a year ago

Sorry - I meant SUM(id_connect='') - see here. It's more "leet" than the wordy case

Bohemian · Accepted Answer · 2012-05-02 11:33:20Z

-1

This works:

SELECT COUNT(DISTINCT(if(id_connect = '', concat(id, 'text not found in id_connect'), id_connect)))
FROM tb1

Output:

See it running here.

edited May 2, 2012 at 11:33

answered May 2, 2012 at 10:41

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

2 Comments

eggyal Over a year ago

@Bohemian: What if a blank's id matches an id_connect?

Bohemian Over a year ago

@eggyal then add something to it (see edited answer) - don't be pedantic

Collectives™ on Stack Overflow

MySQL COUNT with empty column

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related