如何计算MySQL中的唯一设置值

Question

I would appreciate your input to help me count unique values for a SET type in MySql. I have a column named "features" defined as a SET field as follows:

CREATE TABLE cars (features SET('power steering', 'power locks', 'satellite radio', 'power windows', 'sat nav', 'turbo'));

As I fill this table, since the features are not mutually exclusive, I will get records which include a combination of 2 or more of these features. For example:

Car 1 has power steering and power windows, but none of the remaining features. Car 2 has all features. Car 3 has all features, except sat nav and turbo.

What I want to do is to get a list of all single listed features in the table, including the count of records associated to each in a similar fashion as a SELECT statement using a GROUP BY clause. So, following with the example above, I should be able to get the following result:

features       |count
---------------+------
power steering | 3      //All cars have this feature
power locks    | 2      //Only cars 2 and 3 have it
satellite radio| 2      //Only cars 2 and 3 have it
power windows  | 3
sat nav        | 1      //only car 2 has it
turbo          | 1      //only car 2 has it

I have tried using the following query with the expectation of obtaining the aforementioned result:

SELECT features, COUNT(features) FROM cars GROUP BY features;

However, instead of what I was expecting, I got the count of each of the existing feature combinations:

features                                        |count
------------------------------------------------+--------
power steering, power windows                   | 1 //i.e. only 1 car has                 
                                                |   //only these 2 features
                                                |   //(car 1 in this example)
                                                |   
------------------------------------------------+-------
power steering, power locks, satellite radio,   |
power windows, sat nav, turbo                   | 1
------------------------------------------------+-------
power steering, power locks, satellite radio,   |
power windows                                   | 1

So, the question is: Is there a way of obtaining the count of each single feature, as shown in the first table, using one single MySQL query? I could do it by executing one query for each feature, but I'm sure there must be a way of avoiding such hassle. Someone might as well suggest using a different table for the features and joining, but it is not possible at this point without heavily impacting the rest of the project. Thanks in advance!

Answer 1

SELECT set_list.features, COUNT(cars.features) FROM
  (SELECT TRIM("'" FROM SUBSTRING_INDEX(SUBSTRING_INDEX(
  (SELECT TRIM(')' FROM SUBSTR(column_type, 5)) FROM information_schema.columns 
  WHERE table_name = 'cars' AND column_name = 'features'),
  ',', @r:=@r+1), ',', -1)) AS features
  FROM (SELECT @r:=0) deriv1,
  (SELECT ID FROM information_schema.COLLATIONS) deriv2
  HAVING @r <= 
  (SELECT LENGTH(column_type) - LENGTH(REPLACE(column_type, ',', ''))
  FROM information_schema.columns
  WHERE table_name = 'cars' AND column_name = 'features')) set_list
LEFT OUTER JOIN cars
ON FIND_IN_SET(set_list.features, cars.features) > 0
GROUP BY set_list.features

Adapted from:

MySQL: Query for list of available options for SET

My query takes the SQL at the above post as the basis, to get a list of the available column values. All of the indented SQL is that one query, if you execute it alone you'll get the list, and I create a result set from it which I call "set_list". I just copied that query as is, but it is basically doing a lot of string manipulation to get the list - as Mike Brant suggested, the code would be far simpler (but maybe just not as dynamic) if you put the list into another table, and just joined that.

I then join set_list back against the cars table, joining each item from set_list against the rows in cars that contain that feature - FIND_IN_SET(). It's an outer join, so if anything from the set list isn't represented, it will be there with a count of zero.