0

Count occurence of a value in a table

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

4th Jun 2017, 5:21 PM
Kudakwashe Siziva
Kudakwashe Siziva - avatar
4 odpowiedzi
+ 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
5th Jun 2017, 9:03 AM
Krzysztof Przybylowski
Krzysztof Przybylowski - avatar
0
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?
4th Jun 2017, 10:48 PM
Krzysztof Przybylowski
Krzysztof Przybylowski - avatar
0
the whole table. I am picking an abandoned project so need to do some checks before I change things.
5th Jun 2017, 12:09 AM
Kudakwashe Siziva
Kudakwashe Siziva - avatar
0
thank you very much will teet it and advise
5th Jun 2017, 9:15 AM
Kudakwashe Siziva
Kudakwashe Siziva - avatar