Oracle SQL Sum with conditions

Question

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

you want sum(case when <condition> then hours_worked) for each column you're expecting to output the sum of, plus a group by on the other columns being selected, presumably. — Boneist
– Boneist, Commented Jan 21, 2019 at 11:32
P.S. I hope you have a table that contains the departments and that you use that to join your table to in order to get the department name from the department id? — Boneist
– Boneist, Commented Jan 21, 2019 at 11:53
On a sidenote: Never compare dates with strings (account_date > '01/DEC/2017'). The DBMS will try to convert the string to a date which will succeed or fail (with an exception or a wrong date) depending on session settings. Use ANSI date literals instead: account_date > DATE '2017-12-01'. — Thorsten Kettner
– Thorsten Kettner, Commented Jan 21, 2019 at 12:10
@Boneist Thank you for the resolution. I would have accepted your answer however, it was only a comment. We do have a table that has departments however, the data within here is too granular, the recipient of the report wanted multiple codes merged to a 'Trolley Park'. — PAPERCLIPSANDPAPER
– PAPERCLIPSANDPAPER, Commented Jan 21, 2019 at 12:54

dnoeth · Accepted Answer · 2019-01-21 11:44:01Z

3

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"

answered Jan 21, 2019 at 11:44

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PAPERCLIPSANDPAPER Over a year ago

Thank you. I followed Boneist's comment above as a reference however, your answer was the same.

Thorsten Kettner · Accepted Answer · 2019-01-21 12:08:02Z

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.

Thorsten, yeah I could use that as I suspect it may change in the future and instead of updating the PLSQL I could update the table data. Thanks for the information +1

Barbaros Özhan · Accepted Answer · 2019-01-21 12:41:38Z

2

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');

edited Jan 21, 2019 at 12:41

answered Jan 21, 2019 at 11:44

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

Thorsten Kettner Over a year ago

You are missing end for the case expressions. And you cannot group by an alias name defined in the select clause (Department here).

Barbaros Özhan Over a year ago

@ThorstenKettner exactly, the error which I normally don't do, thanks.

Collectives™ on Stack Overflow

Oracle SQL Sum with conditions

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related