mysql need to extract median value from query

Question

I have the following query from which I need to extract the median value of total_views.

    SELECT 
    @rownum:=@rownum + 1 AS row_num, total_views, projectId
FROM
    (SELECT 
        a.creation,
            a.projectId,
            devices,
            browserIds,
            devices + browserIds AS total_views
    FROM
        ((SELECT 
        projectId, creation
    FROM
        event
    WHERE
        kind = 'project_creation'
            AND creation > '2017-04-28') a
    INNER JOIN ((SELECT 
        COUNT(DISTINCT deviceId) AS devices, projectId, creation
    FROM
        event
    WHERE
        kind = 'open' AND component = 'mobile'
    GROUP BY projectId) b
    JOIN (SELECT 
        COUNT(DISTINCT browserId) AS browserIds, projectId, creation
    FROM
        event
    WHERE
        kind = 'open' AND component = 'web'
    GROUP BY projectId) c ON b.projectId = c.projectId) ON a.projectId = b.projectId
        OR a.projectId = c.projectId)
    ORDER BY total_views ASC) d,
    (SELECT @rownum:=0) e
;

This a part of the result :

1   1   151
2   1   256
3   1   301
4   2   404
5   2   305
6   3   895
7   4   654
8   4   369
9   9   874
10  10  123

I need to extend the query to extract the median value of total_views. Any ideas?

Answer 1

Found the solution, needed to use the value of the @rownum variable instead of using the value of the field row_num to determine the position of the middle value. I then calculate the average value of the total_views in the middle of the result set. (Average of two middle values if the result has an even number of lines. average of the middle value if the resultset has an odd number of lines, which is the same as the middle value). thus using the condition :

WHERE row_num in (CEIL(@rownum/2), FLOOR(@rownum/2))

full query:

SELECT avg(total_views) from 
(SELECT 
    @rownum:=@rownum + 1 AS row_num, total_views, projectId
FROM
    (SELECT 
        a.creation,
            a.projectId,
            devices,
            browserIds,
            devices + browserIds AS total_views
    FROM
        ((SELECT 
        projectId, creation
    FROM
        event
    WHERE
        kind = 'project_creation'
            AND creation > '2017-04-28') a
    INNER JOIN ((SELECT 
        COUNT(DISTINCT deviceId) AS devices, projectId, creation
    FROM
        event
    WHERE
        kind = 'open' AND component = 'mobile'
    GROUP BY projectId) b
    JOIN (SELECT 
        COUNT(DISTINCT browserId) AS browserIds, projectId, creation
    FROM
        event
    WHERE
        kind = 'open' AND component = 'web'
    GROUP BY projectId) c ON b.projectId = c.projectId) ON a.projectId = b.projectId
        OR a.projectId = c.projectId)
    ORDER BY total_views ASC) d,
    (SELECT @rownum:=0) e) f WHERE row_num in (CEIL(@rownum/2), FLOOR(@rownum/2))
;