CODEWITHSUNDEEP
oracle-databaseoracle-apex
V. M.
V. M.

Reputation: 3

How to complete report derived from another query with zeros or nulls

So, I'm really having a hard time with a report. I need a report grouped by year. For example, we want to show how many cars are sold per year. So, the report has a column Year, the type/model of the car, and the quantity. However, I also want to show a row with null/zero value, so even when no car of a specific type was sold, I want it to show the row, but with 0. The problem is, this query is based on a lot of views, which shows each transaction. So, my actual query works fine except it doesn't show a type when none of that type was sold in a year. When I pivot this report using Oracle APEX, it almost works. It shows all the types, but if I filter per year, then they are gone.

I have all the years I need, but I don't have the data for that year. I take all the data from multiple views with the specifics of the sales. Some of the models/types were not sold in some years, so when I query the report, it doesn't show up, which is expected. For example:

What I get is

//YEAR - MODEL - QUANTITY //  
2018 - MODEL 1 - 300
2018 - MODEL 2 - 12
2017 - MODEL 1 - 12
2017 - MODEL 2 - 33
2017 - MODEL 3 - 22

What I want

//YEAR - MODEL - QUANTITY //
2018 - MODEL 1 - 300
2018 - MODEL 2 - 12
2018 - MODEL 3 - 0
2017 - MODEL 1 - 12
2017 - MODEL 2 - 33
2017 - MODEL 3 - 22

Any ideas?

Upvotes: 0

Views: 55

Answers (1)

Scott
Scott

Reputation: 5035

You can conjure rows, and outer join to them.

with years as (
  select add_months(date '1980-1-1', (rownum-1)*12) dt 
  from dual 
  connect by level < 5
)
select y.dt, count(e.hiredate) 
from scott.emp e
right outer join years y
on y.dt = trunc(e.hiredate,'yy')
group by y.dt

DT                  COUNT(E.HIREDATE)
------------------- -----------------
01-01-1982 00:00:00                 1
01-01-1983 00:00:00                 0
01-01-1981 00:00:00                10
01-01-1980 00:00:00                 1

Upvotes: 2

Related Questions