Skip to Main Content
  • Questions
  • How to use conditional case with aggregate function

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Eva.

Asked: June 07, 2018 - 11:00 am UTC

Last updated: June 11, 2018 - 7:15 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I have a query like the one below:
SELECT CH.BLNG_NATIONAL_PRVDR_IDNTFR
CASE
WHEN CH.TCN_DATE BETWEEN TO_DATE('01-JAN-2016','DD/MM/YYYY') AND TO_DATE('30-JUN-2016','DD/MM/YYYY')
THEN ROUND(SUM(CH.PAID_AMOUNT)/COUNT(DISTINCT CH.MBR_IDENTIFIER),2)
END
FROM AD_CLAIM_HEADER CH
WHERE CH.BSNS_STATUS_CID = 71
AND CH.BLNG_PRVDR_LCTN_TXNMY_CODE = '282N00000X'
GROUP BY CH.BLNG_NATIONAL_PRVDR_IDNTFR;


Can an aggregate function be used in the THEN and then grouped? Please help!

and Connor said...

Yes, you can put the aggregate function *around* the CASE statement, taking advantage of the fact that a 'false' case will return null in the absence of an 'else' and hence sum only the rows that match the condition, eg

SQL> create table t ( x varchar2(1), y int );

Table created.

SQL>
SQL> insert into t values ('a',10);

1 row created.

SQL> insert into t values ('a',11);

1 row created.

SQL> insert into t values ('a',12);

1 row created.

SQL> insert into t values ('b',10);

1 row created.

SQL> insert into t values ('b',20);

1 row created.

SQL>
SQL> select
  2    sum(case when x = 'a' then y end) sum_of_a,
  3    sum(y) total
  4  from t;

  SUM_OF_A      TOTAL
---------- ----------
        33         63



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.