MySQL: How do you count non empty fields in a row, then count the same values?

Question

Keep in mind that there's no way I can normalize the table, so I'm having to work with what I got. In the table, the rows are similar to this

name  |  widget1  |  widget2 | widget3 
------+-----------+----------+----------
Joe   |   blue    |   red    |          
Jane  |   green   |          |          
Bob   |   red     |   red    |  green
Susy  |   green   |  green   |

What I'd like to do is count the total number of widgets (Joe has 2 widget, Jane has 1, etc), and also count the number of similar widgets (Bob has 3 widgets - 2 red and 1 green, Susy has 2 widgets- 2 green, etc)

Here's my code to count the total number of widgets:

SELECT ( 
         SUM( IF(widget1 <> "", 1, 0) ) +
         SUM( IF(widget2 <> "", 1, 0) ) +
         SUM( IF(widget3 <> "", 1, 0) )
       ) AS totalWidgets FROM table

Which works fine. But is there a better way to do this? Also, to count the number of similar of values, I'm sure I can so something similar but just check if the values are equal... but it could get pretty long and convoluted.

Is there a more direct approach?

700 Software · Accepted Answer · 2011-02-10 15:53:20Z

2

Have not tested, but it should work

select name, count(1), SUM(widget = 'red') reds, SUM(widget = 'green') greens, SUM(widget = 'blue') blues
from
  (
    (SELECT name, widget1 widget FROM table)
   UNION
    (SELECT name, widget2 widget FROM table)
   UNION
    (SELECT name, widget3 widget FROM table)
  )q
WHERE widget <> ''
group by name;

All on one line

select name, count(1), SUM(widget = 'red') reds, SUM(widget = 'green') greens, SUM(widget = 'blue') blues from ((SELECT name, widget1 widget FROM table) UNION (SELECT name, widget2 widget FROM table) UNION (SELECT name, widget3 widget FROM table))q WHERE widget <> '' group by name;

q is our normalized "table" (not really a table, but it kinda looks like one).

See our normalized table this way

select * from ((SELECT name, widget1 widget FROM table) UNION (SELECT name, widget2 widget FROM table) UNION (SELECT name, widget3 widget FROM table))q;

Not sure what you call it, I think it is a subquery. (I have used MySQL for years and I still don't know the proper names)

answered Feb 10, 2011 at 15:53

700 Software

88.3k89 gold badges244 silver badges349 bronze badges

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

1 Comment

TerryMatula Over a year ago

Brilliant! Worked perfectly. HeeHaw!

Collectives™ on Stack Overflow

MySQL: How do you count non empty fields in a row, then count the same values?

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