How to return a record of 0 for sums of time periods where there was no record.

From: John <google_at_johnmee.com>
Date: 27 Nov 2002 16:08:46 -0800
Message-ID: <feaafaee.0211271608.6c7b087a@posting.google.com>

Hi,

Back in september I posted for help with an odd query; the challenge was to return a monthly summation which includes a zero value for months during which there is no record at all.

ie. not this...

---

01/11/2002              20010               15003               
01/01/2003              100000              50000               
01/03/2003              500000              250000              
01/04/2003              200000              100000              
                        573000              277830              

but this...

---

01/11/2002              20010               15003               
01/12/2002              0                   0              
01/01/2003              100000              50000               
01/02/2003              0                   0                   
01/03/2003              500000              250000              
01/04/2003              200000              100000              
                        573000              277830              

The original thread is here...

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=feaafaee.0209152245.74090477%40posting.google.com

but I can't add to that thread no more... so here's a new one.

I never posted a final solution, and here I am just a couple of months later having to solve it again (in a different workplace). Thus, so that I don't need to nut it out a third time, I now post a working solution.

select trunc(month,'month') as month,

       sum(contract_value) as contract_value,
       sum(win_value) as win_value

from (

• ensure zeroed months are returned select trunc(add_months(sysdate, rownum-1),'month') as month, 0 as contract_value, 0 as win_value from all_tab_columns where rownum <= 6

    union     

• and return the values we need select trunc(opp_fall_date,'month') as month, sum(opp_value) as contract_value, round(sum(opp_value * opp_chance /100)) as win_value from opps where opp_bus_unit <= 2 and opp_status in (3,4,5) group by trunc(opp_fall_date,'month') ) group by trunc(month,'month')

Notes:
That undated last row is a sum of all records with null in opp_fall_date. This example sums the upcoming 6 months. You should probably change "all_tab_columns" to some other minor table for efficiency's sake, and all credit remains with Candido Dessanti.

thanks,
john. Received on Wed Nov 27 2002 - 18:08:46 CST