Vivek Anand Vivek Anand - 5 months ago 36
SQL Question

Redshift :Sum of distinct values over partition by

How do i add the the distinct marks alone of a student id in student table in Redshift?

In oracle this works,

SELECT SUM(distinct marks) OVER (PARTITION BY studentid) FROM student;


But this doesnt work in Redshift ! I want to solve this without joins with SELECT statement alone.

Answer

You have to either use a JOIN or a correlated query when window functions are not available.

Correlated query(Only selects)

SELECT t.* ,
       (SELECT sum(distinct marks) FROM student s 
        WHERE s.studentid = t.studentid) as stud_sum
FROM student t