Issue while creating case expression

Question

I am facing issues when I try to create a case expression with a group function condition along with normal column conditions.

Table: STG

+ -------+--------+------+----------+
| Ref_ID | Actual | Paid | Reason   |
+ -------+--------+------+----------+
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 480  | TRAINING |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
+ -------+--------+------+----------+

Already the code is like below:

Explanation: If Actual hrs per day exceeds 480 then that needs to be taken as 480. ie. 8hrs/day * 60 =480. then the sum of actual is extended 1440 then that needs to be deducted from the sum of actual hrs.

In this same query, I have to sum the all actuals along with paid (paid hrs if reason is Training)

 SELECT
    CASE WHEN (SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END))>1440 THEN
    ((SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END))-1440) ELSE 0
    END SINGLE_RATE
    FROM STG WHERE REF_ID='H1'
    GROUP BY REF_ID;

I tried to modify like below:

SELECT
    CASE WHEN (SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END)+(case when reason = 'training' then paid else 0 end ))>1440 THEN
    ((SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END)+(case when reason = 'training' then paid else 0 end ))-1440) ELSE 0
    END SINGLE_RATE
    FROM STG WHERE REF_ID='H1'
    GROUP BY REF_ID;

but getting below error:

    ORA-00979: not a GROUP BY expression
    00979. 00000 -  "not a GROUP BY expression"
    *Cause:
    *Action:

Also tried like:

SELECT
    (sum ( (CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END) + case when reason = 'training' then paid else 0 end )-1440) SINGLE_RATE
    FROM STG WHERE REF_ID='H1'
    GROUP BY REF_ID
    having sum ( (CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END) + case when reason = 'training' then paid else 0 end )>1440;

but the paid amount is not getting calculated. Only actual is added.

Please give your suggestions.

Answer 1

From your business rules, you always want to include actual in the sum() calculation but only include paid when the reason is 'training'. That rule is quite easy to express in a case statement. In addition you have a cap that actual cannot exceed 480. That rule can be satisfied with a least() function:

select ref_id
      , sum ( least(actual, 480) + case when reason = 'training' then paid else 0 end ) as tot
from stg
group by ref_id
/

To be frank I am still not clear what you are trying to achieve with the 1440; life would be easier if you had posted some sample data which covered all the cases you wanted to handle and your desired output derived from that sample data. But I am going to guess anyway:

with cte as (
    select ref_id
          , sum ( least(actual, 480) + case when reason = 'training' then paid else 0 end ) as tot
    from stg
    group by ref_id
    )
select ref_id
       case 
          when tot <= 1440 then tot
          else tot - 1440
       end as adjusted_tot
from cte
/

---

As before, if this does not provide the answer you want please edit your question. The clearer your requirement the more likely you are to get a timely answer.