decode with analytic function

[gazal]

i am trying to create a report which gives the number of complaints recieved lastmonth, thismonth, thisyear, till today per category. the query is:

select distinct category,
decode(reg_date,SUBSTR(ADD_MONTHS(sysdate,-1),4,6),cnt,0) LastMonth,
decode(reg_date,SUBSTR(sysdate,4,6),cnt,0) ThisMonth,
decode(substr(reg_date,5,2),substr(sysdate,8,2),cnt,0) ThisYear
from
(select distinct category, reg_date,sum(cnt1) over( partition by reg_date, CATEGORY )as cnt
from
( SELECT distinct dept_name category, SUBSTR (register_date,4,6) reg_date, count(*) cnt1
FROM AM_COMPLAINT_DTL d, am_complaint_register r , am_v_maintenance_dept m where item_status=0
and d.complaint_id=r.complaint_id
and nvl(PREVENTIVE_YN,'N')='N'
and r.maint_dept_id=m.maint_dept_id
group by dept_name, SUBSTR(register_date,4,6)
order by dept_name)
order by category, reg_date desc)

when i run this query i get 2 rows per category for each decode function.how can i eliminate it.

 

[maswien]

I don't see it is necessary to use analytic function here, for your question
aggregate function should do it.

SELECT dept_name category, 
     sum(decode(reg_date,SUBSTR(ADD_MONTHS(sysdate,-1),4,6),1, 0)) LastMonth,
     sum(decode(reg_date,SUBSTR(sysdate,4,6),1, 0)) ThisMonth,
     sum(decode(substr(reg_date,5,2),substr(sysdate,8,2),1, 0)) ThisYear
    FROM AM_COMPLAINT_DTL d, am_complaint_register r , am_v_maintenance_dept m 
    where item_status=0 
     and d.complaint_id=r.complaint_id 
     and nvl(PREVENTIVE_YN,'N')='N' 
     and r.maint_dept_id=m.maint_dept_id 
    group by dept_name
    order by dept_name