Let's say, for simplicity sake, I have the following table:
| ID | amount | p_id | date |
| 1 | 5 | 1 | "2020-01-01T01:00:00" |
| 2 | 10 | 1 | "2020-01-01T01:10:00" |
| 3 | 15 | 2 | "2020-01-01T01:20:00" |
| 4 | 10 | 3 | "2020-01-01T03:30:00" |
| 5 | 10 | 4 | "2020-01-01T03:50:00" |
| 6 | 20 | 1 | "2020-01-01T03:40:00" |
Here's a sample response I want:
{
"2020-01-01T01:00:00": 25, #this is from adding records with ids: 2 and 3
"2020-01-01T03:00:00": 55 #this is from adding records with ids: 3,4,5 and 6
}
I want to write an SQL query that gets the total (sum) amount of all unique p_id's grouped by the hour. The p_id chosen is the record with the latest date. So for example, the first value in the response above doesn't include id 1 because the record with id 2 has the same p_id and the date on that record is later. The one tricky thing is I want to include the summation of all the amount per p_id if their date is before the hour presented. So for example, in the second value of the response (with key "2020-01-01T03:00:00"), even though id 3 has a datestamp in a different hour, it's the latest of that p_id (2) and therefore gets included when summing the amount in "2020-01-01T03:00:00". But since id 6 has p_id 1, record with id 2 doesn't get included, since record with id 6 overrides that p_id. Any help would be greatly appreciated!
Back