return 1 when multiple conditions meet-CodePudding

I have a table that looks like this:

ACCOUNT       hour_count
A             24
B             24
C             23
D             22

I want to create an sql statement that just returns "1" when ALL conditions meet:

when account is A, hour_count = 24
when account is B, hour_count = 24
when account is C, hour_count > 22
when account is D, hour_count > 22

How can I achieve this?

I tried using a CASE statement, but i couldn if 't figure out how to include multiple conditions. It wouldn't work with an AND within a single WHEN statement:

SELECT
CASE
    WHEN ((ACCOUNT = 'A' AND hour_count = 24) )
    THEN 1
END
FROM hour_counts

CodePudding user response：

if you only a single 1 returned if all of the conditions met else nothing returned

with cte as (
select case
          when account in (‘A’,’B’) and hour_count = 24) then 1 
          when account in (‘C’,’D’) and hour_count > 22) then 1
       end as c_count
  from hour_count)
select 1 as one       
  from cte
having sum(c_count)=4 —- if all conditions met

CodePudding user response：

You can turn each check into an exists predicate in a where clause:

(Optional sample test data):

create or replace table T1 as 
select 
COLUMN1::string as "ACCOUNT",
COLUMN2::float as "HOUR_COUNT"
from (values
('A',24),
('B',24),
('C',23),
('D',22)
);

Query with exists predicates for each check:

select 1 as CHECKS where
exists (select 1 from T1 where ACCOUNT = 'A' and HOUR_COUNT = 24) and
exists (select 1 from T1 where ACCOUNT = 'B' and HOUR_COUNT = 24) and
exists (select 1 from T1 where ACCOUNT = 'C' and HOUR_COUNT > 22) and
exists (select 1 from T1 where ACCOUNT = 'D' and HOUR_COUNT > 22)
;

Note that with the test data this will not return a row. That's because it fails on the check for account D. You can change the check to >= or change the value for D to see the effect of passing the exists checks.

CodePudding user response：

If you just need it to return 1 or 0, it gets easier

select iff(count(distinct account)=4,1,0) 
from t
where (account in ('A','B') and hour_count = 24) or 
      (account in ('C','D') and hour_count > 22)