mssql PERCENTILE_CONT group by year I am trying to prepare a dataset for box chart which requires MAX, MIN, MEDIAN and quartiles grouped by years. This is my attempt, however I could not group it by years:
SELECT
    years,
    MAX(NET_MERIT) AS high_end,
    max(q3) as q3,
    max(MEDIAN) as median,
    max(q1) as q1,
    min(NET_MERIT) as low_end

    from(

    select Year(FA_BDATE) as years ,NET_MERIT,
    PERCENTILE_CONT(0.25) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q1,
    PERCENTILE_CONT(0.50) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as MEDIAN,
    PERCENTILE_CONT(0.75) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q3
    from HerdAnalytics_tbl
    group by Year(FA_BDATE)
    order by Year(FA_BDATE)

    

    ) as sub
    order by years

The table is like this: db&lt;&gt;fiddle




FA_BDATE
NET_MERIT




01-01-2009
175.23


01-01-2008
185.23


01-01-2009
195.23


01-01-2010
165.23




And I have a small question: is PERCENTILE_CONT(0.50) is the median itself?
Yes, using PERCENTILE_CONT(0.50) is a way of finding the median. You can check the Basic syntax example section of the PERCENTILE_CONT doc to see an example.
About the GROUP BY, the PARTITION BY clause is already creating groups of years, so you don't need to use a group by on the subquery. Also, you shouldn't use ORDER BY on that subquery, or you'll get the error:

The ORDER BY clause is invalid in views, inline functions, derived
tables, subqueries, and common table expressions, unless TOP, OFFSET
or FOR XML is also specified.

Try your query like this:
SELECT
    years,
    MAX(NET_MERIT) AS high_end,
    MAX(q3) AS q3,
    MAX(MEDIAN) AS median,
    MAX(q1) AS q1,
    MIN(NET_MERIT) AS low_end

FROM(

    SELECT YEAR(FA_BDATE) AS years ,NET_MERIT,
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS MEDIAN,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q3
    FROM HerdAnalytics_tbl    

) AS sub
GROUP BY years
ORDER BY years;