Aggregate function calls may not be nested ERROR

Question

I am trying to run this query in postgresql but getting this error: "ERROR: Aggregate function calls may not be nested". I am not sure what the problem or a solution for this.

Here is my query:

select a.KEY_NBR, a.MAIN_ID
        , 
            case
                when a.KEY_NBR = a.MAIN_ID
                    then 'Don''t Use'
                when a.count = MAX(a.count) over(partition by a.KEY_NBR)
                    then 'Good'
                else 'Bad'
            end [flag]
    from MYTABLE a

`MAX` is an aggregate function, and you might want the result of `MAX(a.count)` to be returned by a `SELECT` statement. — Radu Gheorghiu, Oct 22 '13 at 14:35
What version of PostgreSQL are you using? Tested similar query in 9.2 - it worked ok. — alexius, Oct 22 '13 at 15:05
I am actually using Workbench: Aginity Workbench for Netezza 4.0.998.20231 and it uses postgresql — moe, Oct 22 '13 at 15:10
This question appears to be better suited for http://dba.stackexchange.com/. — Dave Jarvis, Oct 03 '14 at 22:17

score 2 · Accepted Answer · answered Oct 23 '13 at 07:21

2

Your query looks ok and should work. Perhaps the version you use has some limitations. Try this variation:

WITH a AS
  ( SELECT key_nbr, main_id, count,
           MAX(count) OVER (PARTITION BY key_nbr) AS max_count
    FROM mytable
  ) 
SELECT a.key_nbr, a.main_id,
       CASE WHEN a.key_nbr = a.main_id
                THEN 'Don''t Use'
            WHEN a.count = max_count 
                THEN 'Good'
            ELSE 'Bad'
       END AS flag
FROM a ;

answered Oct 23 '13 at 07:21

ypercubeᵀᴹ

113,259
19
174
235

You don't have a column named `count`? Your code indicates that you do have. – ypercubeᵀᴹ Oct 23 '13 at 13:26

Aggregate function calls may not be nested ERROR

1 Answers1