SQL Developer

select * from (your query) t
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = t.label

Thanks User_H3J7U
What do mean with your query?
The full select statement?

select * from (select
    null
  , sum(f.comision)         value
  , trunc(f.efectiva, 'MM') label
from
    risk_facturas f
where
    f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
and f.organizacion_id = :APP_GET_ORGANIZACION_ID
group by
    trunc(f.efectiva, 'MM')) t
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = t.label

Like this?
Thanks again!

Hi,
Am getting this error:
Thanks,

Replace "f.label" with:

trunc(f.efectiva, 'MM')

Label does not exist as a column on the risk_facturas table, it is just an alias and cannot be used in the same query except on the ORDER BY clause. Or you can use it on a higher level (parent) query, like this:

select * from ( select
    null AS NullValue
  , sum(f.comision)         AS value
  , trunc(f.efectiva, 'MM') AS label
from
    risk_facturas f
where
    f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
and f.organizacion_id = :APP_GET_ORGANIZACION_ID
group by
    trunc(f.efectiva, 'MM') ) oq
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = oq.label

Hi Fernigrini,
Thanks for your help!
I will try it that way.
Thanks

with t as (
            select  0 comision,
                    trunc(add_months(sysdate,1-level),'mm') efectiva
              from  dual
              connect by level < 14
           union all
            select  comision,
                    trunc(efectiva,'MM')
              from  risk_facturas
              where fefectiva >= add_months(trunc(sysdate),-13)
                and efectiva < trunc(sysdate) + 1
                and organizacion_id = :APP_GET_ORGANIZACION_ID
          )
select  null          NullValue,
        sum(comision) value,
        efectiva      label
  from  t
  group by efectiva
  order by efectiva
/

SY.
-- correction: f. is removed

Hi SY
Thanks for the complete code and your help.
I appreciate it.

Hi Sy
Can you help me with the same query but returning the last 5 year and grouping by year?
I need the query group by year and returning all last 5 years no matter if it has any production
Return 0 If it does not have any production for that year.

Thanks a lot for your help!

Hi fac586,
Yes, that is what I figured and am using the setting you mentioned to me but having other problems that I posted in the same thread.
Thanks always for you help!

You need to define 5 years. Do you mean from January 1, 2018 or from March 12, 2017? I'll assume latter:

with t as (
            select  0 comision,
                    add_months(trunc(sysdate,'yy'),(1-level) * 12) efectiva
              from  dual
              connect by level <= 6
           union all
            select  comision,
                    trunc(efectiva,'yy')
              from  risk_facturas
              where fefectiva >= add_months(trunc(sysdate),-60)
                and efectiva < trunc(sysdate) + 1
                and organizacion_id = :APP_GET_ORGANIZACION_ID
          )
select  null          NullValue,
        sum(comision) value,
        efectiva      label
  from  t
  group by efectiva
  order by efectiva
/

SY.

And if you what from January 1, 2018:

with t as (
            select  0 comision,
                    add_months(trunc(sysdate,'yy'),(1-level) * 12) efectiva
              from  dual
              connect by level <= 5
           union all
            select  comision,
                    trunc(efectiva,'yy')
              from  risk_facturas
              where fefectiva >= add_months(trunc(sysdate,'yy'),-48)
                and efectiva < trunc(sysdate) + 1
                and organizacion_id = :APP_GET_ORGANIZACION_ID
          )
select  null          NullValue,
        sum(comision) value,
        efectiva      label
  from  t
  group by efectiva
  order by efectiva
/

SY.

Hi SY,
I mean five years back from the end of the current year.
Thanks a lot for your help!

Actually, basd on

efectiva < trunc(sysdate) + 1

in your original query year ago would be March 13, not March 12, so you'd need to consired adding 1 to

fefectiva >= add_months(trunc(sysdate,'yy'),-60)

or

fefectiva >= add_months(trunc(sysdate,'yy'),-48)

SY.

if is today would be:
From 01-JAN-2018
To: 31-DEC-2022
Those 5 years
Thanks

Yes,
The difference is that in the other query I was grouping by month and taking 12 months back.
Here am grouping by years taking 5 years back.
Thanks

Thanks a lot SY.