Count occurence of a value in a table

Question

I want to count the occurence of the word 'yes' in a table of 9 columns. can anyone help!!

Answer 1

Assuming you have a table named "test_table" with four text fields named test_column_1, test_column_2, test_column_3, test_column_4 and you want to count all fields in this table which have 'yes' as a value you can use this query:

SELECT SUM(sum_list.sub_sum) AS total_sum_of_yes
FROM (
SELECT COUNT(test_column_1) as sub_sum FROM test_table WHERE test_column_1 = 'yes' UNION ALL
SELECT COUNT(test_column_2) as sub_sum FROM test_table WHERE test_column_2 = 'yes' UNION ALL
SELECT COUNT(test_column_3) as sub_sum FROM test_table WHERE test_column_3 = 'yes' UNION ALL
SELECT COUNT(test_column_4) as sub_sum FROM test_table WHERE test_column_4 = 'yes'
) AS sum_list

This query should work in all kind of SQL databases but depend on what kind of database you use it could use a list of tables to make it as a little simpler.
I hope this one will help you

Answer 2

If you have more than one occurence of 'yes' word in your table then you should think about redundancy in your database.
But of course you can have it for the reason.
The question is do you want to count the occurence in whole table or in particular record?

Answer 3

the whole table. I am picking an abandoned project so need to do some checks before I change things.

Answer 4

thank you very much will teet it and advise