Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (120 points)

Let's say, for simplicity sake, I have the following table:

IDamountp_iddate
151
"2020-01-01T01:00:00"
2101
"2020-01-01T01:10:00"
3152
"2020-01-01T01:20:00"
4103
"2020-01-01T03:30:00"
5104
"2020-01-01T03:50:00"
6201
"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!

Please log in or register to answer this question.

Browse Categories

...