Given a set of boolean columns how can I get the top 5 columns with the most amount of true values-CodePudding

CREATE TABLE test(
column1 BOOLEAN,
column2 BOOLEAN,
column3 BOOLEAN
);
INSERT INTO test VALUES (true, false, true);
INSERT INTO test VALUES (true, false, true);
INSERT INTO test VALUES (true, false, false);
INSERT INTO test VALUES (true, false, false);
INSERT INTO test VALUES (true, false, true);
INSERT INTO test VALUES (true, false, true);

I am looking for a result where I get

Highest Number of Trues in descending order: Column1, Column3, Column2. In specific, I need the name of the columns. Any idea on how I could do that?

CodePudding user response：

Use UNION ALL to get a resultset from your table with only 2 columns: the name of each column as a string and its value.
Then use 2 levels of aggregation and with GROUP_CONCAT() get the result that you want:

SELECT GROUP_CONCAT(name ORDER BY total DESC) result
FROM (
  SELECT name, SUM(col) total
  FROM (
    SELECT 'column1' name, column1 col FROM test
    UNION ALL
    SELECT 'column2', column2 FROM test
    UNION ALL
    SELECT 'column3', column3 FROM test
  ) t
  GROUP BY name
) t;

If you want the column names in different rows:

SELECT name, SUM(col) total
FROM (
  SELECT 'column1' name, column1 col FROM test
  UNION ALL
  SELECT 'column2', column2 FROM test
  UNION ALL
  SELECT 'column3', column3 FROM test
) t
GROUP BY name
ORDER BY total DESC;

See the demo.

If there are actually more columns and you want the names of the top 5 columns, replace:

GROUP_CONCAT(name ORDER BY total DESC)

with:

SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY total DESC), ',', 5)

and:

ORDER BY total DESC

with:

ORDER BY total DESC LIMIT 5