Calculating median with Group By in AWS Redshift
The following will get you exactly the result you are looking for:
SELECT distinct
subject, median(num_students) over(partition by Subject)
FROM
course
order by Subject;
You simply need to remove the "over()" portion of it.
SELECT subject, median(num_students) FROM course GROUP BY 1;