Eliminate Nulls from Results in Oracle

Hello All,

I have the following query.
I'm getting the values I want, but the employee number is showing up 4x per employee.
I was wondering if there was a way I could get all the numbers to appear on one line per employee instead of 4 lines.

Current results
Emp    MCEE    MCER    SSEE   SSER    
1          1000      NULL    NULL   NULL  
1          NULL     2000     NULL   NULL  
1          NULL     NULL    3000    NULL  
1          NULL     NULL     NULL   4000  

Expected Results
1         1000     2000    3000    40000

SELECT emp.employee,
      (CASE
          WHEN pyd.ded_code = 'MCEE' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS MCEE,
      (CASE
          WHEN pyd.ded_code = 'MCER' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS MCER,
      (CASE
          WHEN pyd.ded_code = 'SSEE' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS SSEE,
      (CASE
          WHEN pyd.ded_code = 'SSER' THEN SUM(pyd.ded_amt)
          ELSE NULL
          END ) AS SSER--,
          --SUM(pyd.ded_amt) AS SUM_DED_AMT

       
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;
metalteckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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,
...
slightwv (䄆 Netminder) Commented:
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;
metalteckAuthor Commented:
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"
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
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
/

Open in new window

awking00Information Technology SpecialistCommented:
No need to wrap -
select 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)) SSE2
from yourtable
group by employee;
awking00Information Technology SpecialistCommented:
Typo - SSE2 should be SSER
slightwv (䄆 Netminder) Commented:
I knew I was over-thinking it....
awking00Information Technology SpecialistCommented:
We're all guilty of that :-)
metalteckAuthor Commented:
@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;
slightwv (䄆 Netminder) Commented:
>>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

Open in new window

metalteckAuthor Commented:
@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 (䄆 Netminder) Commented:
I'm not understanding.

Given my test table and sample data, the select from awking00 provides your expected results.

Please add to my test case to show where it isn't working.

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
 ,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 tab1
 group by employee; 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
metalteckAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Why are you grouping by pyd.ded_code?  Notice in my ecample I do not.
metalteckAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>>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.
awking00Information Technology SpecialistCommented:
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.
awking00Information Technology SpecialistCommented:
Typing too slow again :-)
metalteckAuthor Commented:
Thanks for all your help guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.