MySQL: how do you consider non-empty fields in a string and then count the same values?

Question

Keep in mind that I cannot normalize the table, so I need to work with what I have. In the table, rows are similar to these

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

What I would like to do is to calculate the total number of widgets (Joe has 2 widgets, 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 is my code for calculating 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 great. But is there a better way to do this? Also, in order to count the number of similar values, I'm sure I can do something similar, but just check if the values ​​are equal ... but it can get quite long and confusing.

Is there a more direct approach?

Answer 1

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 in 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 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 this is a subquery. (I have used MySQL for many years, but I still do not know the proper names)