ORA-00937 when reading a group by subquery

Question

I am relatively new to Oracle Db, and this might be something stupid, but I cannot find what is the problem with this query. I get ORA-00937: not a single-group group function when I run it.

select a.col_a, a.col_b, a.amt
from (
    select col_a, col_b, sum(col_c) as amt
    from table_a
    group by col_a, col_b
) a
where a.amt <> 0;

The subquery, by itself, works. To make matters worse, this also works:

select *
from (
    select col_a, col_b, sum(col_c) as amt
    from table_a
    group by col_a, col_b
) a;

This is obviously an illustration, I cannot post the actual code, but please trust me that there are valid reasons to organize the code in this manner.

I have also tried using CTE for the subquery, but nothing changed. Wrapping select * as another subquery also didn't help.

The weird thing is that the error suggests that I am using an aggregate function without a correct group by clause, but the subquery that does aggregation works.

I could work around this using a temp table, but it seems wasteful.

Answer 1

There is nothing wrong with code, as @tim-biegeleisen notes.

After thinking myself crazy, I tried to execute it from SQL Developer instead of TOAD, and it works just fine.

The "problem" appears to stem from the fact that the code is in a .pkb file.

• If I try to run just this statement using "Execute/compile statement" from TOAD, it returns the error.
• If I run it using "Execute as script" it works; the package also compiles.
• If I run it in a separate sql tab, also works.

I will leave this here, in hopes it will appear in someone's google search and save them a ton of time.