Oracle SQL formatting

Question

I Have a query to get bill data, grouped by the calendar year and the week of the year. I want to have the evolution of bills in total. So not only the number of bills of 1 week but from the beginning until the week. I have following query

SELECT DD.CAL_YEAR, DD.WEEK_OF_YEAR AS "Date by week", SUM(DISTINCT FAB.ID) OVER ( ORDER BY DD.CAL_YEAR, DD.WEEK_OF_YEAR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Number of bills"
    FROM BILLS_TABLE FAB
    JOIN DIM_DATE DD ON FAB.BALANCE_DATE_ID = DD.ID
    GROUP BY  DD.CAL_YEAR,DD.WEEK_OF_YEAR;

But when I execute this query I get following exception:

Error: ORA-30487: ORDER BY not allowed here

SQLState: 99999 ErrorCode: 30487

The order by clause is needed for the OVER function, so what is wrong?

sstan · Accepted Answer · 2015-10-07 12:19:59Z

4

The problem is really the use of distinct inside sum.

Because you have the order by clause, that means you are doing a cumulative sum. Performing a distinct in that case doesn't really make any sense. That's what the error is actually trying to tell you.

The documentation for the SUM analytic function mentions this:

If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.

edited Oct 7, 2015 at 12:19

answered Oct 7, 2015 at 12:10

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonas Over a year ago

Ok and when I drop the distinct I get another Error: ORA-00979: not a GROUP BY expression

sstan Over a year ago

An analytic function doesn't work the same way as a normal aggregate function. Your analytic function can only refer to columns in your group by clause.

Collectives™ on Stack Overflow

Oracle SQL formatting

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related