CODEWITHSUNDEEP
oracle-databaseora-00979
David Fletcher
David Fletcher

Reputation: 305

Oracle COUNT(DISTINCT expr) causes ORA-00979 error

I have the following query:

SELECT
   t.f1
   , COUNT(DISTINCT t.f2) AS distinct_count
FROM
   some_table t
GROUP BY
   t.f1

which works fine. However, if I attempt to do this, it gives me a ORA-00979 not a GROUP BY expression error.

WITH my_aggregate_data AS
(
   SELECT
      t.f1
      , COUNT(DISTINCT t.f2) AS distinct_count
   FROM
      some_table t
   GROUP BY
      t.f1
)

SELECT
   *
FROM
   ( SELECT f1, distinct_count FROM my_aggregate_data )
PIVOT
   (
      SUM(distinct_count)
      FOR f1
      IN (a, b, c)
   )

If I simply remove the DISTINCT key word from the COUNT method, it works fine (except that the count is a total count and not a distinct count).

This seems like a fairly straight forward query, but I have been unable to locate anyone else documenting this issue (which probably means I'm doing something wrong).

I also found nothing in the Oracle documentation that indicates that COUNT(DISTINCT exp) would behave differently than COUNT(exp) other than the obvious distinction between the two.

I have also written it this way

SELECT
   *
FROM
   (
      SELECT
         t.f1
         , COUNT(DISTINCT t.f2) AS distinct_count
      FROM
         some_table t
      GROUP BY
         t.f1
   )
PIVOT
   (
      SUM(distinct_count)
      FOR f1
      IN (a, b, c)
   )

but it just results in the same ORA-00979 error, and again, if you remove the DISTINCT key word, everything works (except that it gives me the wrong answer).

Upvotes: 1

Views: 335

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

I don't have your sample data and the actual query, so cannot say for sure why ORA-00979 is raised. But, as far as the query you posted is concerned, the problem is in your PIVOT expression and nothing else.

IN (a, b, c)

This throws the error

ORA-56901: non-constant expression is not allowed for pivot|unpivot values

which means the values specified in the IN clause of PIVOT should be constants (string literals/ numbers etc)

If you use 

IN ('a', 'b', 'c')

It works perfectly fine.

Have a look at this DEMO to see both the error message and working query.

Upvotes: 1

Related Questions