How to resolve query exception when using median aggregation for more than one column in Redshift data source

This article guides you through the steps to resolve the issue “query exception thrown when using median aggregation for more than one column” when creating a Redshift data source in Bold BI.

Issue Details

When working with Redshift data sources, you may encounter a query exception when trying to use median aggregation for more than one column. This is because, by default, median aggregation is designed to work with only one column.

Error Message

Sf_Exception - Unable to retrieve data table. Reason: One or more errors occurred. (There was an error executing the query SELECT                     EXTRACT(MONTH FROM((("36d9dd7553443295e1136c1bfff28f"."exam_date")  + interval '-0 minutes')::timestamp)) AS "column8",                    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "36d9dd7553443295e1136c1bfff28f"."order_complete") AS "column1",                 AVG("36d9dd7553443295e1136c1bfff28f"."order to patient arrived tat") AS "column2",                 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "36d9dd7553443295e1136c1bfff28f"."patient arrived to exam begin tat") AS "column3",                 (AVG((CAST("36d9dd7553443295e1136c1bfff28f"."order_begin_acquisition" AS NUMERIC)))/NULLIF(86400,0)) AS "column4",                                (AVG((CAST("36d9dd7553443295e1136c1bfff28f"."exam begin to exam complete tat" AS NUMERIC)))/NULLIF(60,0)) AS "column5"FROM "public"."view_external_cube_exam_facts" AS "36d9dd7553443295e1136c1bfff28f"WHERE ( ( ("36d9dd7553443295e1136c1bfff28f"."organization_id" IN(  22, 7, 59 )))) AND ( (EXTRACT(YEAR FROM((("36d9dd7553443295e1136c1bfff28f"."exam_date")  + interval '-0 minutes')::timestamp)) IN(  2021 ))) AND ( ("36d9dd7553443295e1136c1bfff28f"."hl7 modality" IN(  'CT' )))GROUP BY EXTRACT(MONTH FROM((("36d9dd7553443295e1136c1bfff28f"."exam_date")  + interval '-0 minutes')::timestamp))ORDER BY "column8" ASC LIMIT 201 . Reason: 0A000: within group ORDER BY clauses for aggregate functions must be the same)

Solution

For Redshift, if you need to apply median aggregation to more than one column, you will need to do so in separate widgets. This means that each column you wish to apply the median aggregation to must be handled individually.

Additional References

For more detailed information on how median aggregation works in Redshift, please refer to the PERCENTILE_CONT documentation documentation.