How to calculate median from comma separated string values in MySQL?

Question

Problem is to calculate the median of values that are distributed in a table. I have multiple values in a table column against the same uids, Where I need to find the median of those values for each uid. In order to do that, first I concatenated the values and then trying to find the median among the grouped values against each uid. After group concatenation, I have got the values into a comma separated list.

Now, I need to find the median from the comma separated values given in the following table in column "text_responded_in_hour".

uId |text_responded_in_hour 
----|-----------------------
176 |70,660,70
177 |102      
194 |102,102  

I have tried Google but didn't able to find any related solution.

Answer 1

The following code for calculating medians can work for even and odd number of values. This piece of code served the purpose :)

SELECT  
uId,    
date,    
(SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(responded_text_time_in_hour ORDER BY responded_text_time_in_hour),
            ',',
            ((
                ROUND(
                    LENGTH(GROUP_CONCAT(responded_text_time_in_hour)) - 
                    LENGTH(
                        REPLACE(
                            GROUP_CONCAT(responded_text_time_in_hour),
                            ',',
                            ''
                        )
                    )
                ) / 2) + 1
            )),
            ',',
            -1
        ) +
        SUBSTRING_INDEX( 
            SUBSTRING_INDEX( 
                GROUP_CONCAT(responded_text_time_in_hour ORDER BY responded_text_time_in_hour), ',', (COUNT(*)/2) )
                , ',', -1))/2 as median

FROM outTable
WHERE
    (responded_text_time_in_hour>0 AND responded_text_time_in_hour <=3600)
GROUP BY 1,2