Eliminate Nulls from Results in Oracle

Use MAX around the case and tweak the group by if necessary.

...
MAX (CASE
          WHEN pyd.ded_code = 'MCEE' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS MCEE,
...

Worst case, wrap your current select with an outer one:

select employee, max(MCEE) MCEE, MAX(MCER) MCER, MAX(SSEE), SSEE, MAX(SSER)  SSER    
from
(
--your current select
)
group by employee;

I've tried both scenarios, but keep on getting this error message:
ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

I had a misplaced comma in the second one:  MAX(SSEE), SSEE

Here is a tested version:

drop table tab1 purge;
create table tab1(employee number, ded_code char(4), ded_amt number);

insert into tab1 values(1,'MCEE',500);
insert into tab1 values(1,'MCEE',500);
insert into tab1 values(1,'MCER',2000);
insert into tab1 values(1,'SSEE',3000);
insert into tab1 values(1,'SSER',4000);
commit;

select employee, max(MCEE) MCEE, MAX(MCER) MCER, MAX(SSEE) SSEE, MAX(SSER)  SSER    
from (
SELECT employee,
      (CASE
          WHEN ded_code = 'MCEE' THEN SUM(ded_amt)
          ELSE NULL
          END ) AS MCEE,
      (CASE
          WHEN ded_code = 'MCER' THEN SUM(ded_amt)
          ELSE NULL
          END ) AS MCER,
      (CASE
          WHEN ded_code = 'SSEE' THEN SUM(ded_amt)
          ELSE NULL
          END ) AS SSEE,
      (CASE
          WHEN ded_code = 'SSER' THEN SUM(ded_amt)
          ELSE NULL
          END ) AS SSER
FROM  TAB1
group by employee, ded_code
)
group by employee
/

Select allOpen in new window

Typo - SSE2 should be SSER

I knew I was over-thinking it....

We're all guilty of that :-)

@slightwv, when I run your code, I keep on getting column ambiguously defined error.
awking00, when I run your code, I get invalid identifiers

Ideally, this is what I'm trying to aim for since this query is one that I will need to reference in a bigger one.  Problem I'm getting with the code as is that it not properly ended.
select tax.employee, max(tax.MCEE) AS MCEE ,max(tax.MCER) AS MCER, max(tax.SSEE) AS SSEE, max(tax.SSER) AS SSER
from (


SELECT emp.employee,

          sum(decode(pyd.ded_code, 'MCEE',pyd.ded_amt)) MCEE,
          sum(decode(pyd.ded_code, 'MCER',pyd.ded_amt)) MCER,
          sum(decode(pyd.ded_code, 'SSEE',pyd.ded_amt)) SSEE,
          sum(decode(pyd.ded_code, 'SSER',pyd.ded_amt)) SSE2
/*      (CASE
          WHEN pyd.ded_code = 'MCEE' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS MCEE1,
      (CASE
          WHEN pyd.ded_code = 'MCER' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS MCER1,
      (CASE
          WHEN pyd.ded_code = 'SSEE' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS SSEE1,
      (CASE
          WHEN pyd.ded_code = 'SSER' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS SSER1*/


       
FROM   PROD.EMPLOYEE emp,
       PROD.PAYDEDUCTN pyd
         
WHERE emp.company = pyd.company
and   emp.employee = pyd.employee
and   pyd.payroll_year = 2015

AND  NOT(emp.EMP_STATUS LIKE 'AT%'
      OR emp.EMP_STATUS LIKE 'PN%'
      OR emp.EMP_STATUS LIKE 'SR%'
      OR emp.EMP_STATUS LIKE 'T%')
     
AND (pyd.DED_CODE='MCEE'
      OR pyd.DED_CODE='MCER'
      OR pyd.DED_CODE='SSEE'
      OR pyd.DED_CODE='SSER')
     
     

group by emp.employee,pyd.ded_code
) as tax
order by tax.employee;

>>when I run your code, I get invalid identifiers

He corrected the typo SSE2 should be SSER.



Don't try to combine mine with awking00's.

Use his as-is.

SELECT emp.employee,
           sum(decode(pyd.ded_code, 'MCEE',pyd.ded_amt)) MCEE,
           sum(decode(pyd.ded_code, 'MCER',pyd.ded_amt)) MCER,
           sum(decode(pyd.ded_code, 'SSEE',pyd.ded_amt)) SSEE,
           sum(decode(pyd.ded_code, 'SSER',pyd.ded_amt)) SSER
 FROM   PROD.EMPLOYEE emp,
        PROD.PAYDEDUCTN pyd
 WHERE emp.company = pyd.company
 and   emp.employee = pyd.employee
 and   pyd.payroll_year = 2015
 AND  NOT(emp.EMP_STATUS LIKE 'AT%' 
       OR emp.EMP_STATUS LIKE 'PN%' 
       OR emp.EMP_STATUS LIKE 'SR%' 
       OR emp.EMP_STATUS LIKE 'T%') 
       
 AND (pyd.DED_CODE='MCEE' 
       OR pyd.DED_CODE='MCER' 
       OR pyd.DED_CODE='SSEE' 
       OR pyd.DED_CODE='SSER')
 group by emp.employee

Select allOpen in new window

@slightvw, the inner query works with either the case statement or decode.
Its when I'm trying to get the max that I'm running into issues.

If I use this code, I'm getting a "not single-group group function" error

select tax.employee, max(tax.MCEE) AS MCEE ,max(tax.MCER) AS MCER, max(tax.SSEE) AS SSEE, max(tax.SSER) AS SSER
from (


                SELECT emp.employee,
               
                          sum(decode(pyd.ded_code, 'MCEE',pyd.ded_amt)) MCEE,
                          sum(decode(pyd.ded_code, 'MCER',pyd.ded_amt)) MCER,
                          sum(decode(pyd.ded_code, 'SSEE',pyd.ded_amt)) SSEE,
                          sum(decode(pyd.ded_code, 'SSER',pyd.ded_amt)) SSER
           
                       
                FROM   PROD.EMPLOYEE emp,
                       PROD.PAYDEDUCTN pyd
                         
                WHERE emp.company = pyd.company
                and   emp.employee = pyd.employee
                and   pyd.payroll_year = 2015
               
                AND  NOT(emp.EMP_STATUS LIKE 'AT%'
                      OR emp.EMP_STATUS LIKE 'PN%'
                      OR emp.EMP_STATUS LIKE 'SR%'
                      OR emp.EMP_STATUS LIKE 'T%')
                     
                AND (pyd.DED_CODE='MCEE'
                      OR pyd.DED_CODE='MCER'
                      OR pyd.DED_CODE='SSEE'
                      OR pyd.DED_CODE='SSER')
                     
                     
               
                group by emp.employee,pyd.ded_code
        ) tax
        order by tax.employee;

slightwv, the inline query, which gets the sum of each record compiles with no errors,  but I'm still getting 4 records for each employee. I've attached a screenshot of the results I'm getting with this code.
I'm trying to remove the nulls and have everything on one line. But when I try using the max function, I'm getting not a single-group group function error.

I'm sorry if I was unclear and hope this clarifies things.

This is your query I used to get the results in the screenshot.
SELECT emp.employee,
 sum(decode(ded_code, 'MCEE',ded_amt)) MCEE,
 sum(decode(ded_code, 'MCER',ded_amt)) MCER,
 sum(decode(ded_code, 'SSEE',ded_amt)) SSEE,
 sum(decode(ded_code, 'SSER',ded_amt)) SSER
 
FROM   PROD.EMPLOYEE emp,
       PROD.PAYDEDUCTN pyd
         
WHERE emp.company = pyd.company
and   emp.employee = pyd.employee
and   pyd.payroll_year = 2015

AND  NOT(emp.EMP_STATUS LIKE 'AT%'
      OR emp.EMP_STATUS LIKE 'PN%'
      OR emp.EMP_STATUS LIKE 'SR%'
      OR emp.EMP_STATUS LIKE 'T%')
     
AND (pyd.DED_CODE='MCEE'
      OR pyd.DED_CODE='MCER'
      OR pyd.DED_CODE='SSEE'
      OR pyd.DED_CODE='SSER')

GROUP BY emp.employee, pyd.ded_code
order by emp.employee;
tax.docx

Why are you grouping by pyd.ded_code?  Notice in my ecample I do not.

slightwv, that was it.
I'm sorry, I just overlooked that small detail.

I always thought you had to have the field you are summing in the group by.

>>I always thought you had to have the field you are summing in the group by.

Opposite:  You need to group by all non-aggregate fields.

As slightwv points out, you're getting four rows because of the grouping by ded_code. Remove it from the query and see what you get then.

Typing too slow again :-)

Thanks for all your help guys.