ERROR: within group ORDER BY clauses for aggregate functions must be the same -- Redshift

[jamesnestler]

Knowledge Drop

Last tested: May 6, 2020
 

You are probably either using multiple MEDIAN() functions working on different columns together, or are using multiple other sort based aggregate functions together.

Per Redshift documentation:

If a statement includes multiple calls to sort-based aggregate functions (LISTAGG, PERCENTILE_CONT, or MEDIAN), they must all use the same ORDER BY values. Note that MEDIAN applies an implicit order by on the expression value.

So, if you have two measures like so:

measure: bad1 {

type: median

sql: column_1 ;;

}

measure: bad2 {

type: median

sql: column_2 ;;

}

You'll receive the ERROR: within group by... error.

To work around this:

• Do the MEDIAN() in a CTE
• Do the MEDIAN() in a subquery
• Only use one MEDIAN at a time
• Use PERCENTILE_CONT() window function in a measure type: number. Note: this approach is NOT recommended, see this post for the caveats and reasons why.

Source, see 'Usage Notes' here: https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html

This content is subject to limited support.