CODEWITHSUNDEEP
sqlpostgresqlcountsubquerycase
nirnroot
nirnroot

Reputation: 724

CASE statement subquery row count to be used as the result

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.

Upvotes: 0

Views: 1320

Answers (3)

stackUser
stackUser

Reputation: 579

As you are using case so I am assuming you want second column value only when there is no subtable.column1value = '100' record

select 
(CASE WHEN (SELECT true FROM subtable WHERE subtable.column1value = '100' limit 1) THEN (SELECT COUNT(*) FROM subtable WHERE subtable.column1value = '100')
      WHEN  (SELECT true FROM subtable2 WHERE subtable2.column2value = '100' limit 1) THEN (SELECT COUNT(*) FROM subtable2 WHERE subtable2.column2value = '100')
      ELSE 0 END) as column_name

Upvotes: 0

JustMe
JustMe

Reputation: 2373

If you want to have it in one column just do:

COALESCE(
  nullif((SELECT COUNT(*) FROM subtable WHERE subtable.column1value = '100'), 0),
  (SELECT COUNT(*) FROM subtable WHERE subtable.column1value = '100')
)

First null if first count is zero (coalesce will move on to second query), then count second table - if no records then will be 0 at the end.

Upvotes: 0

GMB
GMB

Reputation: 222432

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.

Upvotes: 1

Related Questions