This discussion is archived
5 Replies Latest reply: Jan 30, 2012 8:10 AM by user4685506 RSS

SQL aggregate ( sub select )

user4685506 Newbie
Currently Being Moderated
Hi * ,

I have the following problem :

One view which contains the inventory of sales volume based on month and year ( numbers )

example :

month Year volume
-------- ------ -----------
1 2008 105
2 2008 107
3 2008 102
.. 2008 ..
12 2008 130
.. 2009 ..
1 2011 220
2 2011 210
9 2011 250
12 2011 270
1 2012 290

I am trying to build a query to represent the growth of sales to be used in a graph , i.e comparison between the sales of current month ( last month ) of the current year ( last year ) and the last sales of last month of last years .

would somebody be able to help ?

thanks
  • 1. Re: SQL aggregate ( sub select )
    882395 Explorer
    Currently Being Moderated
    This may help:
    select
      lag(VOLUME) over (order by YEAR, MONTH) PREVIOUS_MONTH,
      lag(VOLUME) over (partition by MONTH order by YEAR) LAST_YEAR_SAME_MONTH,
      last_value(VOLUME) over (partition by YEAR order by MONTH  range between unbounded preceding and unbounded following) LAST_MONTH_CURRENT_YEAR
    from
      SALES
  • 2. Re: SQL aggregate ( sub select )
    user4685506 Newbie
    Currently Being Moderated
    Thanks , but the I am not so deep in the mining techiques and i found your sql a bit difficult for me :-(

    This is the sql which I used in my graph presentation , till December ot was Ok , but as of January 2012 , it will not work

    select year , ROUND(sum(volume)) total_in_month froms ales
    where month=12
    group by year
    order by year ;

    this would output the following :

    2007     25
    2008     30
    2009     59
    2010     101
    2011     197

    it will not output the volume in January ( 1) in 2012

    how would I rewrite your sql ?
  • 3. Re: SQL aggregate ( sub select )
    882395 Explorer
    Currently Being Moderated
    Your SQL obviously does not work because it only outputs the december values.
    Try this:
    select
      YEAR,
      round(sum(VOLUME) keep (dense_rank last order by MONTH))
    from
      SALES
    group by
      YEAR
    order by
      YEAR
    The "keep (dense_rank last order by MONTH)" makes that only the last month of every year is taken into account.
  • 4. Re: SQL aggregate ( sub select )
    user4685506 Newbie
    Currently Being Moderated
    Perfect :-) !! the SQL outputs as expected , but I have problem how to present your update in APEX chart .
    This was the original sql i used in Apex chart :

    select null link,
    t.year label,
    ROUND(sum(t.volume)) value
    from sales t
    where month=12
    group by t.year
    order by t.year

    I am trying to wrap it as following , but looks incorrect :

    select null link,
    t.year label,
    ROUND(sum(t.volume)) keep (dense_rank last order by monat)) value
    from sales t
    group by t.year
    order by t.year
  • 5. Re: SQL aggregate ( sub select )
    user4685506 Newbie
    Currently Being Moderated
    Hi ,

    i am trying to apply the same concept on another column ( product_id ) to see the growth of product sales :

    select year ,count(distinct product_id) keep (dense_rank last order by monat))
    from sales
    group by year
    order by year ;

    but using DISTINCT semms to be not allowed .
    Do you have a workaround ?

    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points