Vivek Anand Vivek Anand - 1 year ago 353
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download