Skip to main content
waffle.svg
Domo Knowledge Base

Finding the Median of a Value

  1. Last updated
  2. Save as PDF

You can use an SQL DataFlow to find the median of a value. You can do this in either a MySQL or Redshift DataFlow.

MySQL

Transform_data_1:

select  * ,@row := @row + 1 as 'row count'  from input_dataset, (select @row:=0) a  order by `value`

Transform_data_2:

SELECT *, (CASE  WHEN (SELECT MAX(`row count`) FROM transform_data_1) % 2 = 0   THEN    ((SELECT `value` FROM transform_data_1 WHERE `row count` = (((SELECT MAX(`row count`) FROM transform_data_1)/2)+1)) + (SELECT `value` FROM transform_data_1 WHERE `row count` = (((SELECT MAX(`row count`) FROM transform_data_1)/2))) ) /2        WHEN (SELECT MAX(`row count`) FROM transform_data_1) % 2 != 0   THEN (SELECT `value` from transform_data_1 WHERE `row count` = (((SELECT MAX(`row count`) FROM transform_data_1)+1)/2)   )      END) AS 'Median'    FROM transform_data_1

Your output is then: 

SELECT * FROM transform_data_2

Redshift

MEDIAN ( median_expression ) OVER ( [ PARTITION BY partition_expression ] )