Lets supose I have a tabla A like:

I would like to be able to count the number of times any of these elements (x,y,z) appears in the cols (bisac1,bisac2,bisac3).

So, the expected result should be 3 for the first 3 rows, 2 for the next 3 and 1 for the last 3.

CodePudding user response：

Seems the following should do what you require?

select 
    case when bisac1 in ('x','y','z') then 1 else 0 end  
    case when bisac2 in ('x','y','z') then 1 else 0 end  
    case when bisac3 in ('x','y','z') then 1 else 0 end 
from t;

CodePudding user response：

You can also use one case per letter instead of one case per column (Stu's approach). The result will be the same for your sample data:

SELECT 
  CASE WHEN 'x' IN (bisac1, bisac2, bisac3) THEN 1 ELSE 0 END  
  CASE WHEN 'y' IN (bisac1, bisac2, bisac3) THEN 1 ELSE 0 END  
  CASE WHEN 'z' IN (bisac1, bisac2, bisac3) THEN 1 ELSE 0 END
FROM yourtable;

The result will not be the same if the same letter occurs in different columns, For example, if your row looks like this:

Then Stu's query will produce 3 as result, my query here 2. From your description, it is unclear to me if your sample data can contain such rows at all or if the two queries will always create the same result for your data.

And even if your data can include such rows, it's still unclear to me whether you want to get 3 or 2 as result.

So, summarized, it's up to you what exactly you want to use here.