Results 1 to 4 of 4

Thread: Running Total

  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Running Total

    Hello,

    I was wondering if someone could help me with hwo to calculate a running total correctly

    I have the following code and I ahve highlighted the line which I would like to show as a running total.

    When I run the code I get the following error:

    Error: ORA-00979: not a GROUP BY expression
    (State1000, Native Code: 3D3)

    Could someone help me with why I am getting this error message. I have trawled the internet and cannot work out why I am erroring, as I have limited knowledge on running totals

    SELECT
    SUM(COUNTING.COUNT_CANC) CASES_CANCELLED,
    SUM(COUNTING.COUNT_NON_CANC) ACTUAL_CASES_DONE,
    SUM(COUNTING.COUNT_CANC + COUNTING.COUNT_NON_CANC) AS PLANNED_CASES,
    SUM(COUNTING.COUNT_CANC + COUNTING.COUNT_NON_CANC) OVER( ORDER BY COUNTING.WEEK_COMMENCING) AS TESTING_CUMULATIVE,
    COUNTING.WEEK_COMMENCING
    FROM(
    SELECT
    THEATRE_NO,
    SESSION_TYPE,
    VISIT_DATE,
    REASON_CANCELLED,
    CASE WHEN REASON_CANCELLED IS NOT NULL THEN 1 ELSE 0 END AS COUNT_CANC,
    CASE WHEN REASON_CANCELLED IS NULL THEN 1 ELSE 0 END AS COUNT_NON_CANC,
    OUTCOME,
    TO_CHAR(VISIT_DATE+ (1-TO_CHAR(VISIT_DATE,'D'))) WEEK_COMMENCING
    FROM THEATRE_VISIT) COUNTING
    GROUP BY COUNTING.WEEK_COMMENCING

    Any ideas?

    Thanks
    Helen

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Wink

    This should resolve the problem: *Note the aggregate function (SUM) within the window function (SUM..OVER..).
    Code:
    SELECT COUNTING.WEEK_COMMENCING, 
           SUM(COUNTING.COUNT_CANC) CASES_CANCELLED,
           SUM(COUNTING.COUNT_NON_CANC) ACTUAL_CASES_DONE,
           SUM(COUNTING.COUNT_CANC + COUNTING.COUNT_NON_CANC) AS PLANNED_CASES,
           SUM(SUM(COUNTING.COUNT_CANC + COUNTING.COUNT_NON_CANC)) OVER( ORDER BY COUNTING.WEEK_COMMENCING) AS TESTING_CUMULATIVE
           --SUM(COUNTING.COUNT_CANC + COUNTING.COUNT_NON_CANC) OVER( ORDER BY COUNTING.WEEK_COMMENCING) AS TESTING_CUMULATIVE,
      FROM (SELECT THEATRE_NO,
                   SESSION_TYPE,
                   VISIT_DATE,
                   REASON_CANCELLED,
                   CASE WHEN REASON_CANCELLED IS NOT NULL THEN 1 ELSE 0 END AS COUNT_CANC,
                   CASE WHEN REASON_CANCELLED IS NULL THEN 1 ELSE 0 END AS COUNT_NON_CANC,
                   OUTCOME,
                   TO_CHAR(VISIT_DATE+ (1-TO_CHAR(VISIT_DATE,'D'))) WEEK_COMMENCING
              FROM THEATRE_VISIT) COUNTING
    GROUP BY COUNTING.WEEK_COMMENCING

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Lightbulb

    Here is an additional refactored version:
    Code:
    SELECT TRUNC(VISIT_DATE, 'DAY') WEEK_COMMENCING, 
           COUNT(*) AS PLANNED_CASES,
           COUNT(NVL2(REASON_CANCELLED, NULL, 'X') ACTUAL_CASES_DONE,
           COUNT(REASON_CANCELLED) CASES_CANCELLED,
           SUM(COUNT(*)) OVER (ORDER BY TRUNC(VISIT_DATE, 'DAY')) AS TESTING_CUMULATIVE
      FROM THEATRE_VISIT
    GROUP BY TRUNC(VISIT_DATE, 'DAY')
    Unfortunately you did not provide a table layout or test data, so I am guessing. In theory, this should be right though.

    I assume your VISIT_DATE column is in fact defined as a DATE column and not a varchar2.

    Note that COUNT(*) returns the count of rows; the COUNT on a null column value not add to the count.

    Note that TRUNC of a date with 'DAY' as format model will return the date of the beginning of the week.

  4. #4
    Join Date
    Dec 2011
    Posts
    82
    Thank you so much for your help

    It works a treat!

    Thanks
    Helen

Posting Permissions

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