MySQL count (and sum) fields in multiple columns where 'not empty'

Question

I'm using this MySQL query

SELECT COUNT(pros_a) + COUNT(pros_b) + COUNT(pros_c) + COUNT(pros_d) + COUNT(pros_e) AS pros_total FROM my_table WHERE contentid = 'id'

to count and sum fields in each column. But this returns ALL fields (full and empty). I want to count only filled fields, excluding empty fields. How can I do that? Just a little bit... confused! :)

Any help would be very appreciated! Thanks

EDIT:

my_table

pros_a   pros_b   pros_c   pros_d   pros_e
-------  ------   ------   ------   ------
good     (empty)  good     good     (empty)

expected result

pros_total
----------
3

What is an empty field for you?

Mosty Mostacho
– Mosty Mostacho

2012-03-06 14:44:54 +00:00
Commented Mar 6, 2012 at 14:44 — Mosty Mostacho
– Mosty Mostacho, Commented Mar 6, 2012 at 14:44
Can you post sample data and expected output?

Salman Arshad
– Salman Arshad

2012-03-06 14:48:49 +00:00
Commented Mar 6, 2012 at 14:48 — Salman Arshad
– Salman Arshad, Commented Mar 6, 2012 at 14:48
@Salman A Yeah, sorry. Just edited.

DavidF
– DavidF

2012-03-06 15:04:48 +00:00
Commented Mar 6, 2012 at 15:04 — DavidF
– DavidF, Commented Mar 6, 2012 at 15:04

ypercubeᵀᴹ · Accepted Answer · 2012-03-06 15:07:19Z

1

The COUNT(column) function does not count fields that are NULL. It counts empty (zero-length) strings though.

You can change these into:

COUNT( CASE WHEN column <> '' THEN column END )

or:

SUM(column <> '')

The last works in MySQL because TRUE is evaluated as 1 and FALSE as 0.

answered Mar 6, 2012 at 15:07

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

Comments

Teja · Accepted Answer · 2012-03-06 14:46:12Z

0

SELECT COUNT(DISTINCT pros_a) + COUNT(DISTINCT pros_b) + COUNT(DISTINCT pros_c) + COUNT(DISTINCT pros_d) + COUNT(DISTINCT pros_e) FROM my_table WHERE contentid = 'id'

The above one should work.

answered Mar 6, 2012 at 14:46

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Comments

Crsr · Accepted Answer · 2012-03-06 14:47:06Z

0

Use where condition for empty fields, for example WHERE pros_a != '' or pros_a != '0' if is NULL. pros_a != '' AND pros_b != ''

answered Mar 6, 2012 at 14:47

Crsr

6243 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL count (and sum) fields in multiple columns where 'not empty'

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related