Count how many times a value is present in the table

Question

I have a table with more than 390 columns.

I would like to count how many times de value 'Y' are present in the table.

Most of the columns may have this value, some not.

I don't know how to perform it, as it has several columns.

Is there a way to perform it?

Thanks

This should point you in the right direction [https://stackoverflow.com/questions/639531/search-in-all-fields-from-every-table-of-a-mysql-database/47761287#47761287](https://stackoverflow.com/questions/639531/search-in-all-fields-from-every-table-of-a-mysql-database/47761287#47761287) — Mark B, May 24 '18 at 20:09
`SELECT COUNT(CASE WHEN col1 = 'Y' THEN 1 END) + COUNT(CASE WHEN col2 = 'Y' THEN 1 END) + ... + COUNT(CASE WHEN col390 = 'Y' THEN 1 END)` — Salman A, May 24 '18 at 20:19

score 1 · Answer 1 · answered May 24 '18 at 20:13

1

Wow thats alot of columns.

You will have to do it in a loop in your language of choice (pseudo-code):

while ($rows = mysql_query("SELECT * FROM TABLE")) {
  foreach ($col in $rows) {
     if ($col == 'Y') {
        $count++;
     }
  }
}
print("Count: $count");

Its probably a good idea to try to normalize your table structure so that you do not have so many columns in 1 table

answered May 24 '18 at 20:13

d g

1,594
13
13

What language is this? – Salman A May 24 '18 at 20:17
Pseudo-code, which means he's presenting the *algorithm*, not the actual code. You'll have to rewrite in your language of choice. – Lasse V. Karlsen May 24 '18 at 20:40
ha sorry my psedo code is hybrid php & python. Ill fix it up for php and update the post – d g May 24 '18 at 20:41

t.. · Answer 2 · 2018-05-24T20:38:38.697

2 different sql ways here, BUT... you have to put the column names in

    select Columns, count(*) From 
    (select Column1  as Columns from your_table where Column1 = 'Y'
    union all
    select Column2  as Columns from your_table where Column2 = 'Y'
    union all
    select Column3 as Columns from your_table where Column3 = 'Y'
    union all
    select Column4  as Columns from your_table where Column4 = 'Y'
    union all
    select Column5  as Columns from your_table where Column5 = 'Y'
    union all
    select Column6  as Columns from your_table where Column6 = 'Y')myTab
    group by Column;

or

select value
from yourtable
unpivot
(
  value
  for col in (column1, column2, column3, column4,...etc)
) un
where col = 'Y'

Count how many times a value is present in the table

2 Answers2