PHP-SQL: Counting and sorting if values set in table

Question

I have a table with following stucture:

Now I would like to count the amount of "yes" per column over a not defined amount of lines and output a list in descending order: C3 -> 3x C1 -> 1x ...

with something like this i can get the number of yes'es but can not order the list:

count(case when C_1 = '[\"yes\"]' then 1 end) C1_count

Is there an elegant way to loop and sort true the table count and sort to generate the output list? And also be able in cas that C_11 will be added to expand the code?

Thanks a lot for assistance.

. . I added the MySQL tag based on your comment. However, in general, you should tag your questions with the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Feb 23, 2021 at 12:07

Stefanov.sm · Accepted Answer · 2021-02-23 10:26:15Z

1

Here is a solution in Postgresql

select col_no, yes_count from unnest
(
 (select array
 [
  count(case when C_1 = 'yes' then 1 end),
  count(case when C_2 = 'yes' then 1 end),
  ...
  count(case when C_10 = 'yes' then 1 end)
 ]::integer[] from _table
 )
) with ordinality as t(yes_count, col_no)
order by yes_count desc;

It seems to me that your table design can be improved. And BTW pls. tag your RDBMS.

edited Feb 23, 2021 at 10:26

answered Feb 23, 2021 at 10:21

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

1 Comment

Francis Over a year ago

Thanks a lot, I am using MySQLi. I will try to apply the code to my table, wich is already there and I need to deal with :(

Gordon Linoff · Accepted Answer · 2021-02-23 12:05:20Z

0

In MySQL, you can use:

select t.*,
       ( (c_1 = 'Yes') + (c_2 = 'Yes') + . . . + (c_10 = 'Yes')
       ) as num_yes
from t
order by num_yes desc;

Actually, I realize that the above assumes that the blank values are actually empty strings, not NULL. To handle NULLs use NULL-safe comparisons:

select t.*,
       ( (c_1 <=> 'Yes') + (c_2 <= >'Yes') + . . . + (c_10 <=> 'Yes')
       ) as num_yes
from t
order by num_yes desc;

answered Feb 23, 2021 at 12:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

PHP-SQL: Counting and sorting if values set in table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related