sql - Select column with the most entries of a specific value in mySQL & Python

Question

How would I find the column with the most entries of a specific value in a mySQL database with Python?

I have the following table:

| product |    id1    |    id2    |    id3    |
-----------------------------------------------
| apple   | 'ordered' |           | 'ordered' |
| banana  |           |           |      ?    |
| bread   | 'ordered' | 'ordered' |           |
| milk    | 'ordered' |           | 'ordered' |

How can I find out which column has the most 'ordered' strings in it?

In this case it would be the column with the name 'id1'.

question from:https://stackoverflow.com/questions/65649272/select-column-with-the-most-entries-of-a-specific-value-in-mysql-python

Answer 1

You can use count as follows:

Select case when greatest(id1,id2,id3) = id1 then 'id1' 
            When greatest(id1,id2,id3) = id2 then 'id2'
            else 'id3' end grt 
  From
(Select count(case when id1 = 'ordered' then 1 end) as id1,
       count(case when id2 = 'ordered' then 1 end) as id2,
       count(case when id3 = 'ordered' then 1 end) as id3
  From t) t