Count and group non-empty values in MySQL

Question

I need to count the non-empty (by which I mean a string containing at least 1 character) rows grouped by a particular ID. Eg. my data might look like this:

form_id   mapping
1         'value_1'
1         ''
1         'value_2'
2         ''
2         NULL
3         'value_3'

and I want to count the non-empty values for each form, so I want the results to look like this:

form_id   mapping_count
1         2
2         0
3         1

If the empty values were all NULL, I guess I could use

SELECT form_id, count(mapping) FROM table GROUP BY form_id

...but that would include zero-length strings in the count, which I don't want.

I could use a where clause to only return rows where a value exists in the mapping column, but I want to return the form IDs that have no mappings, so that is no good either.

I'm guessing I need a subquery of some sort, but am having trouble putting it together.

Quassnoi · Accepted Answer · 2010-02-25 15:10:08Z

20

SELECT  form_id, COUNT(NULLIF(TRIM(mapping), ''))
FROM    mytable
GROUP BY
        form_id

This will not count records that don't contains at least one non-whitespace character (this includes whitespace strings, empty strings and NULLs).

If a non-empty all-whitespace string is valid, use this:

SELECT  form_id, COUNT(NULLIF(mapping, ''))
FROM    mytable
GROUP BY
        form_id

answered Feb 25, 2010 at 15:10

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

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

1 Comment

Russ Over a year ago

Great, thanks for that - I had not come across the NULLIF function before. Exactly what I needed. :)

Collectives™ on Stack Overflow

Count and group non-empty values in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related