Combine multple aggregation queries into a single query-CodePudding

How can I combine these four queries into a single query?

select count(ornon_id) as DEC_AUTH_Y from tbbu_policies where DECL_AUTH_REQ_FLAG = 'Y' and status_code = 'ACTI';
    
select count(ornon_id) as DEC_AUTH_N from tbbu_policies where DECL_AUTH_REQ_FLAG = 'N'and  status_code = 'ACTI';
    
select count(ornon_id) as INV_AUTH_Y from tbbu_policies where INV_AUTH_REQ_FLAG = 'Y' and status_code = 'ACTI';
    
select count(ornon_id) as INV_AUTH_N from tbbu_policies where INV_AUTH_REQ_FLAG = 'N'  and status_code = 'ACTI';

CodePudding user response：

Use conditional aggregation:

SELECT COUNT(CASE WHEN DECL_AUTH_REQ_FLAG = 'Y' THEN ornon_id END) as DEC_AUTH_Y,
       COUNT(CASE WHEN DECL_AUTH_REQ_FLAG = 'N' THEN ornon_id END) as DEC_AUTH_N,
       COUNT(CASE WHEN INV_AUTH_REQ_FLAG  = 'Y' THEN ornon_id END) as INV_AUTH_Y,
       COUNT(CASE WHEN INV_AUTH_REQ_FLAG  = 'N' THEN ornon_id END) as INV_AUTH_N
FROM   tbbu_policies
WHERE  status_code = 'ACTI';

CodePudding user response：

You can use case-when based strategy with four columns, like this:

select
SUM(
CASE
    WHEN DECL_AUTH_REQ_FLAG = 'Y' THEN 1
    ELSE 0
END) as firstcount,
SUM(
CASE
    WHEN DECL_AUTH_REQ_FLAG = 'N' THEN 1
    ELSE 0
END) as secondcount,
SUM(
CASE
    WHEN INV_AUTH_REQ_FLAG = 'Y' THEN 1
    ELSE 0
END) as thirdcount,
SUM(
CASE
    WHEN INV_AUTH_REQ_FLAG = 'N' THEN 1
    ELSE 0
END) as fourthcount
from tbbu_policies
where status_code = 'ACTI' and
      (not (ornon_id is null));