How to count/sum the number of text cells in a row

November 11, 2021

I have a simple modell that stores text values. I like to count how many text cells I have in a single row where the result is not "nincs".

In this case I like to have (kap_bar_01 + kap_bar_02 + kap_bar_03 + kap_bar_04 + kap_bar_05) and the result I need is 3.

I tried this but it works not:

SELECT (kap_bar_01 + kap_bar_02 + kap_bar_03 + kap_bar_04 + kap_bar_05) FROM mymodell

Result is 0.

Thank you in advance!

>Solution :

Assuming the columns are not nullable, you can sum boolean expressions:

SELECT (kap_bar_01 <> 'nincs') + 
       (kap_bar_02 <> 'nincs') + 
       (kap_bar_03 <> 'nincs') + 
       (kap_bar_04 <> 'nincs') + 
       (kap_bar_05 <> 'nincs') AS result 
FROM mymodell;

If they are nullable, use also COALESCE():

SELECT (COALESCE(kap_bar_01, '') <> 'nincs') + 
       (COALESCE(kap_bar_02, '') <> 'nincs') + 
       (COALESCE(kap_bar_03, '') <> 'nincs') + 
       (COALESCE(kap_bar_04, '') <> 'nincs') + 
       (COALESCE(kap_bar_05, '') <> 'nincs') AS result 
FROM mymodell;