CASE statement subquery row count to be used as the result

Question

As of now, I have a CASE-statement of the form

CASE    
        WHEN exists (SELECT * FROM subtable WHERE subtable.column1value = 's100')   
        THEN 'exists in column 1'    
        WHEN exists (SELECT * FROM subtable2 WHERE subtable2.column2value = 's100')    
        THEN 'exists in column 2'   
        ELSE ''   
END

In other words, I have a contract product components that can exist in different places, so I have to search through those tables and columns and I would like to have one column per contract that specifies if that contract has those kind of product components anywhere at all.

Now I would like to change this to keep track of how many components there are per contract and save this value as a result, something like

CASE    
        WHEN exists (SELECT COUNT(*) FROM subtable WHERE subtable.column1value = 's100')
        THEN COUNT(*)
        WHEN exists (SELECT COUNT(*) FROM subtable2 WHERE subtable2.column2value = 's100')
        THEN COUNT(*)
        ELSE 0
END

but this obviously doesn't work. Is there some way to rephrase this so that I can have an alias for the count result of each subquery or is there a better approach altogether? Maybe just have multiple subqueries where I select the count of results and then another column for the sum of these columns?

(SELECT COUNT(*) FROM subtable WHERE subtable.column1value = 's100') AS result_column1,
(SELECT COUNT(*) FROM subtable2 WHERE subtable2.column2value = 's100') AS result_column2,
(result_column1 + result_column2) AS sum_of_results

That would get the job done but doesn't sound very eloquent.

Answer 1

You could use a subquery:

select result_column1, result_column2, result_column1 + result_column2 sum_of_results
from (
    select
        (select count(*) from subtable  where subtable.column1value  = 100) as result_column1,
        (select count(*) from subtable2 where subtable2.column2value = 100) as result_column2
) x

Note that I removed the single quotes around the literal values in the where clause; if these columns are numeric, then they should be compared as such.