View the original community article here
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.