Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Unanswered: COUNT's OVER PARTITION


    I've a table with brand's and dates. Ex:
    XPTA Jan/2012
    XPTA Jan/2012
    XPTA Feb/2012
    XPTO Feb/2012
    XPTA Mar/2012

    and I created a select to give me the count and the accumulated value per month:

    SELECT brand, date, COUNT(brand) qt, SUM(COUNT(brand) OVER (PARTITION BY brand, TO_DATE(TO_CHAR(date,'YYYY'),'YYYY') ORDER BY date ROWS BETWEEN unbounded preceding AND CURRENT ROW) qt_acum FROM table GROUP BY brand, date, TO_DATE(TO_CHAR(date,'YYYY'),'YYYY')

    And this gives me something like this:

    XPTA Jan/2012 2 2
    XPTA Feb/2012 1 3
    XPTO Feb/2012 1 1
    XPTA Mar/2012 1 4

    and here's the problem. Despite XPTO didn't had a record on Mar/2012 the accumulated value should be 1. I need a line like this:

    XPTO Mar/2012 0 1

    Can anyone give some pointers???? Many thanks.

  2. #2
    Join Date
    Mar 2002
    Reading, UK
    One way to do it I think would be to

    1) create a subquery which generates a list of all the brands and does a cartesian join to all of the months. Also add a dummy column called count and set that to 0 and cumulative_count column which you can set to null.
    2) Outer join from the subquery to your existing query on brand and month.
    3) Use nvl to show the count i.e. nvl(yourcount, subquery.count)
    4) For the cumulative count do nvl(yourcumcount, lag...)) where the lag will retrieve the previous cumulative count.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts