5 Replies Latest reply: Jan 30, 2012 10:10 AM by user4685506 RSS

    SQL aggregate ( sub select )

    user4685506
      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 )
          HEP
          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
            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 )
              HEP
              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
                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
                  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