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.