CODEWITHSUNDEEP
sqloracle-databasereport
PAPERCLIPSANDPAPER
PAPERCLIPSANDPAPER

Reputation: 77

Oracle SQL Sum with conditions

I'm attempting to Sum (Present Hours), (Sickness Hours) by Department and month. This sum must include the department they work in which I've done by including a case statement. I've attempted this myself multiple times however, I can't seem to get it to work as expected.

EMP_NO     Wage_type     Hours_worked   Department      OBJID(UNIQUE)  DATE
10011      Normal        14             1063            ABC116         04/01/18
10011      Normal        07             1063            ABC117         05/01/18
10011      SICK          21             1063            ABC118         01/02/18
10030      Normal        12.5           1054            ABC119         02/02/18
10030      SICK          7              1054            ABC120         03/02/18
10030      SICK          7              1054            ABC121         04/02/18

Example code for raw data etc:

Select 
 TPDRS.OBJID
,TPDRS.WAGE_HOURS
,(case 
    when TPDRS.ORG_CODE = 1010 then  'Trolley P1'
    when TPDRS.ORG_CODE = 1011 then  'Trolley P2'
    when TPDRS.ORG_CODE = 1053 then  'Trolley P3'
    when TPDRS.ORG_CODE between 1054 and 1057 then  'Trolley P4'
    when TPDRS.ORG_CODE between 1040 and 1047 then  'Trolley P5'
    when TPDRS.ORG_CODE in ('1063','1064','1065','1068') then 'Trolley P6'
    else 'NOT REQUIRED'
        end) as Department
,TPDRS.EMP_NO
,TPDRS.ACCOUNT_DATE
,EXTRACT(Month from TPDRS.ACCOUNT_DATE) "Month_Number"
,EXTRACT(Year from TPDRS.ACCOUNT_DATE) "Year"

from TrolleyParkAttendant  TPDRS
WHERE TPDRS.ACCOUNT_DATE > '01/DEC/2017'
 AND (TPDRS.WAGE_GRP_DB IN ( 'O', 'N' ) or WAGE_CODE = 'SICK')

Expected Results:
Department    HoursSick     HoursPresent     Month
Trolley P6      0             21              01
Trolley P6     21             0               02 
Trolley P3     14             12.5            02

Any assistance would be appreciated

Upvotes: 0

Views: 2178

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use the following SQL statement with conditional aggregation

SELECT decode(1063,'Trolley P6',1054,'Trolley P3') as Department,
       sum(case when Wage_type='SICK' then Hours_worked end) as HoursSick,
       sum(case when Wage_type='Normal' then Hours_worked end) as HoursPresent,
       to_char(TPDRS.ACCOUNT_DATE,'mm') as "Month"       
  FROM TrolleyParkAttendant  TPDRS
 WHERE TPDRS.ACCOUNT_DATE > '01/DEC/2017'
   AND (TPDRS.WAGE_GRP_DB IN ( 'O', 'N' ) or WAGE_CODE = 'SICK')
 GROUP BY decode(1063,'Trolley P6',1054,'Trolley P3'), 
          to_char(TPDRS.ACCOUNT_DATE,'mm');

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Your data model seems to be lacking both a department table and an organization table. You should probably create them. Well, CTEs (WITH clause) will do for now...

with dept as
(
  select 'P1' as id, 'Trolley P1' as name from dual union all
  select 'P2' as id, 'Trolley P2' as name from dual union all
  select 'P3' as id, 'Trolley P3' as name from dual union all
  select 'P4' as id, 'Trolley P4' as name from dual union all
  select 'P4' as id, 'Trolley P5' as name from dual union all
  select 'P6' as id, 'Trolley P6' as name from dual
)
, org as
(
  select 1010 as code, 'P1' as dept_id from dual union all
  select 1011 as code, 'P2' as dept_id from dual union all
  select 1053 as code, 'P3' as dept_id from dual union all
  select 1054 as code, 'P4' as dept_id from dual union all
  select 1057 as code, 'P4' as dept_id from dual union all
  select 1040 as code, 'P5' as dept_id from dual union all
  select 1047 as code, 'P5' as dept_id from dual union all
  select 1063 as code, 'P6' as dept_id from dual union all
  select 1064 as code, 'P6' as dept_id from dual union all
  select 1065 as code, 'P6' as dept_id from dual union all
  select 1068 as code, 'P6' as dept_id from dual
)
select
  dept.name as department,
  sum(case when tpa.wage_code = 'SICK' then wage_hours end) as hours_sick,
  sum(case when tpa.wage_grp_db in ('O', 'N') then wage_hours end) as hours_present,
  to_char(account_date, 'yyyy-mm') as month
from trolleyparkattendant tpa
join org on org.code = tpa.org_code
join dept on dept.id = org.dept_id
group by dept.name, to_char(account_date, 'yyyy-mm') 
order by month, department;

I took wage_grp_db in ('O', 'N') and wage_code = 'SICK' to be mutually exclusive. Your WHERE clause kind of suggested that. You may have to adopt this, if it is not the case.

If you want to count the non-department records, too, then left outer join dept instead.

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

You need conditional aggregation, to avoid repeating the expressions nest them in a Derived Table (Inline View in Oracle speak):

select
    Department
  ,sum(case when Wage_type = 'SICK' then Hours_worked else 0 end) as HoursSick
  ,sum(case when Wage_type = 'Normal' then Hours_worked else 0 end) as HoursPresent
  ,"Month_Number"
  ,"Year"
from
 (
    Select 
     (case 
        when TPDRS.ORG_CODE = 1010 then  'Trolley P1'
        when TPDRS.ORG_CODE = 1011 then  'Trolley P2'
        when TPDRS.ORG_CODE = 1053 then  'Trolley P3'
        when TPDRS.ORG_CODE between 1054 and 1057 then  'Trolley P4'
        when TPDRS.ORG_CODE between 1040 and 1047 then  'Trolley P5'
        when TPDRS.ORG_CODE in ('1063','1064','1065','1068') then 'Trolley P6'
        else 'NOT REQUIRED'
            end) as Department
    ,EXTRACT(Month from TPDRS.ACCOUNT_DATE) "Month_Number"
    ,EXTRACT(Year from TPDRS.ACCOUNT_DATE) "Year"

    from TrolleyParkAttendant  TPDRS
    WHERE TPDRS.ACCOUNT_DATE > '01/DEC/2017'
     AND (TPDRS.WAGE_GRP_DB IN ( 'O', 'N' ) or WAGE_CODE = 'SICK')
 ) dt
group by
    Department
  ,"Month_Number"
  ,"Year"

Upvotes: 3

Related Questions