Postgresql - should I use over & partition by to get one row per person?

Question

I am new to programming and have been scratching my head over this problem for a few hours now.

In short, I have a dataset in postgresql that looks like this (there are 1000s of rows):

DonatorID   YearofDonation  NoofDonations   TotalAmount
2           1998            1               20
2           2000            2               20
2           2001            4               80
2           2005            3               15
2           2006            1               15
3           1999            1               15
3           2000            3               40
3           2014            1               50
4           2015            2               20

I want to collate the data such that I have one row per donator that summarises the number of donations in a year and the total amount i.e. I would like the data to look like this:

DonatorID 1998Don  1998Amt   1999Don    1999Amt ... 2015Don    2015Amt 
2         1          20        0          0     ...    0        0
3         0           0        1          15    ...    0        0
4         0           0        0          0     ...    2        20

I have tried using partition by but cannot seem to get the desired result. Any help would be much appreciated!

This should piont you in the right direction: http://stackoverflow.com/questions/8027293/postgresql-and-pivot-tables-using-crosstab-function — BlindAndFurious, Jan 31 '16 at 20:07
That worked! Thanks so much :-) I used the sum and case when recommendation — JassiL, Jan 31 '16 at 20:37

Postgresql - should I use over & partition by to get one row per person?

0 Answers0