Oracle Sql Query to Return a Row when there is no Data

Question

I have the following query which displays sum of employees in a particular group (A1,B1..etc) and in a specifc department.

The problem is when there is no data for a specific department at a specific date then that row with the department name is not displayed at all and I want the query to display all the rows/department names even if they have data in it or not. It should display '0' in such a case.

SELECT NVL(TO_CHAR(COALESCE(dept_name,'NA') ),'TOTAL') AS Department,
  SUM (
  CASE
    WHEN ( emp_group IN('ABC','CDE','EFG','GHI'))
    THEN 1
    ELSE 0
  END) AS A1,
  SUM(
  CASE
    WHEN ( emp_group IN ('XYZ'))
    THEN 1
    ELSE 0
  END) AS B1,
  SUM (
  CASE
    WHEN ( emp_group IN ('ABC','CDE','EFG','GHI','XYZ'))
    THEN 1
    ELSE 0
  END) AS TOTAL
FROM emp e
WHERE 
dept_name IN('IT','FI','ACC')
AND e.transaction_date          = trunc(sysdate)
GROUP BY rollup(COALESCE(dept_name,'NA'))

Thanks in advance

Left joins only work when there is an available table. Is there a department table? Is there a foreign key from emp to department? — Dan Bracuk
– Dan Bracuk, Commented Apr 14, 2015 at 12:51

Florin Ghita · Accepted Answer · 2015-04-15 05:36:15Z

1

Start from departments(you should have something like that... ) and do a left join:

SELECT NVL(TO_CHAR(COALESCE(d.dept_name,'NA') ),'TOTAL') AS Department,
  SUM (
  CASE
    WHEN ( emp_group IN('ABC','CDE','EFG','GHI'))
    THEN 1
    ELSE 0
  END) AS A1,
  SUM(
  CASE
    WHEN ( emp_group IN ('XYZ'))
    THEN 1
    ELSE 0
  END) AS B1,
  SUM (
  CASE
    WHEN ( emp_group IN ('ABC','CDE','EFG','GHI','XYZ'))
    THEN 1
    ELSE 0
  END) AS TOTAL
FROM departments d
left join emp e on d.dept_id = e.dept_id
WHERE 
d.dept_name IN('IT','FI','ACC')
AND e.transaction_date          = trunc(sysdate)
GROUP BY rollup(COALESCE(d.dept_name,'NA'));

If you don't have departments you can left join with dual in this manner:

SELECT NVL(TO_CHAR(COALESCE(e.dept_name,'NA') ),'TOTAL') AS Department,
  SUM (
  CASE
    WHEN ( emp_group IN('ABC','CDE','EFG','GHI'))
    THEN 1
    ELSE 0
  END) AS A1,
  SUM(
  CASE
    WHEN ( emp_group IN ('XYZ'))
    THEN 1
    ELSE 0
  END) AS B1,
  SUM (
  CASE
    WHEN ( emp_group IN ('ABC','CDE','EFG','GHI','XYZ'))
    THEN 1
    ELSE 0
  END) AS TOTAL
FROM (select 'IT' as dept_name from dual union all 
      select 'FI' from dual union all
      select 'ACC' from dual) d
left join emp e on d.dept_name = e.dept_name
WHERE 
  e.transaction_date          = trunc(sysdate)
GROUP BY rollup(COALESCE(e.dept_name,'NA'));

edited Apr 15, 2015 at 5:36

answered Apr 14, 2015 at 12:53

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

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

2 Comments

Akshay Over a year ago

No, there is just 1 table and that is 'emp'. How can I achieve it with 1 table now? Thanks.

Akshay Over a year ago

I tried the above query with left join on the same table and on the dual table, but it displays the same result as before.

Collectives™ on Stack Overflow

Oracle Sql Query to Return a Row when there is no Data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related