Skip to content
Advertisement

Returning median values of time deltas across different groups

Trying to calculate the range between different steps in my data-table and return the median for each calculation using this SQL code:

This returns the following error message:

[0A000][500310] Amazon Invalid operation: within group ORDER BY clauses for aggregate functions must be the same; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: Amazon Invalid operation: within group ORDER BY clauses for aggregate functions must be the same;

Note: I can however return one median value.

Here is a sample of my dataframe:

The desired result is the median time delta between one and two and two and three (there are more columns in the actual data)

Advertisement

Answer

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.

From https://docs.aws.amazon.com/redshift/latest/dg/r_PERCENTILE_CONT.html

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement