CODEWITHSUNDEEP
sqloracle-databaseconditional-statements
sandsawks
sandsawks

Reputation: 309

Oracle SQL Conditional Arithmetic

I have a data set that lists an employee id, code, hours and wages. Any 1 employee can have 1 of either OT1 or OT2, or they could have 1 row of both. The short of it is that I need to sum all of the wages, but if they have both codes to just take the amount for OT1. Then I want to divide total wages by the hours in the condition I stated. Example Data:

+ -------+------+-------+--------+
| ID     | CODE | HOURS | AMOUNT |
+ -------+------+-------+--------+
| 123456 | OT1  | 10    | 80     |
| 789000 | OT1  | 8     | 120    |
| 789000 | OT2  | 8     | 60     |
| 654111 | OT2  | 4     | 40     |
+ -------+------+-------+--------+

I'm attempting to add a new column to divide the amount by the hours and will remove the code column so we can sum each employee to have a single record. The catch is, if the employee has both OT1 and OT2, I don't want to sum those , I just want the hours from OT1. That logic manually applied to my previous example

+ -------+-------+--------+---------+
| ID     | HOURS | AMOUNT | AVERAGE |
+ -------+-------+--------+---------+
| 123456 | 10    | 80     | 8       |
| 789000 | 8     | 180    | 22.5    |
| 654111 | 4     | 40     | 10      |
+ -------+-------+--------+---------+

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can do this using conditional aggregation:

select id, 
       coalesce(sum(case when code = 'OT1' then hours end),
                sum(hours)
               ) as hours,
       sum(amount) as amount,
       (sum(amount) / 
        coalesce(sum(case when code = 'OT1' then hours end),
                 sum(hours)
               )
       ) as average
from t
group by id
order by id;

This method explicitly combines values from multiple rows, so it should work as expected if there are duplicates.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You get the hours for the first code with Oracle's KEEP FIRST:

select
  id, 
  min(hours) keep (dense_rank first order by code) as hours,
  sum(amount) as amount,
  round(sum(amount) / min(hours) keep (dense_rank first order by code), 2) as average
from mytable
group by id
order by id;

Upvotes: 1

Related Questions