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.

2 Answers

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

Window functions are famous & influential in Redshift.

Since Redshift DB is a fork of Postgres, most of the Windows functions supported in Postgres 8.x are flexible to use.

for the give SQL, you could write something

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

SQL should work in Redshift. Here is the documentation for all Window function support: