CODEWITHSUNDEEP
oracle-database
Ethan Bradberry
Ethan Bradberry

Reputation: 107

Oracle - Show 0 if no data for the month

i'm trying to show some averages over the past 12 months but there is no data for June/July so i want the titles for the months to display but just 0's in the 3 columns

currently it's only showing August - May which is 10 rows so it's throwing off formulas and charts etc.

    select to_char(Months.Period,'YYYY/MM') As Period, coalesce(avg(ec.hours_reset),0) as AvgOfHOURSReset, coalesce(AVG(ec.cycles_reset),0) as AvgofCycles_Reset, Coalesce(AVG(ec.days_reset),0) as AvgofDAYS_Reset
from (select distinct reset_date as Period from engineering_compliance
where reset_date between '01/JUN/15' and '31/MAY/16') Months
left outer join engineering_compliance ec on ec.reset_date = months.Period
WHERE EC.EO = 'AT CHECK'
group by to_char(Months.Period,'YYYY/MM')
order by to_char(Months.Period,'YYYY/MM')

;
(select distinct to_char(reset_date,'YYYY/MM') as Period from engineering_compliance
where reset_date between '01/JUN/15' and '31/MAY/16') Months;

Upvotes: 1

Views: 123

Answers (1)

Peter M.
Peter M.

Reputation: 713

That query is pretty good, it's not far from working.

You would need to replace the Months table part. You want exactly one row per month, regardless of whether there's any data in the ec table.

You could maybe synthesize some data without going to any actual table in your own schema.

For example:

SELECT
  extract(month from add_months(sysdate,level-1)) Row_Month,
  extract(year from add_months(sysdate,level-1)) Row_Year,
  to_char(add_months(sysdate,level-1),'YYYY/MM') Formatted_Date,
  trunc(add_months(sysdate,level-1),'mon') Join_Date
FROM dual
CONNECT BY level <= 12;

gives:

ROW_MONTH,ROW_YEAR,FORMATTED_DATE,JOIN_DATE
 6,2016,'2016/06',1/06/2016
 7,2016,'2016/07',1/07/2016
 8,2016,'2016/08',1/08/2016
 9,2016,'2016/09',1/09/2016
10,2016,'2016/10',1/10/2016
11,2016,'2016/11',1/11/2016
12,2016,'2016/12',1/12/2016
 1,2017,'2017/01',1/01/2017
 2,2017,'2017/02',1/02/2017
 3,2017,'2017/03',1/03/2017
 4,2017,'2017/04',1/04/2017
 5,2017,'2017/05',1/05/2017

Option 1: Write that subselect inline into your query, replacing sysdate with the start month and the figure 12 on the last line can be altered for the number of months you want in the series.

Option 2 (can be reused more conveniently in a variety of situations and queries): Write a view with a long series of months (for example, Jan 1970 to Dec 2199) using my SQL above. You can then join to that view on join_date with whatever start and end months you want. It will give you one row per month and you can pick up the formatted date from its column.

Upvotes: 1

Related Questions