I need select the sum of values by period from 11:00 to 10:00 of the next day, grouped by ids.
This is my Table1:
date value id
"2017-03-29 11:00:00" "453" "id1"
"2017-03-29 12:00:00" "000" "id1"
"2017-03-29 13:00:00" "234" "id1"
"2017-03-29 14:00:00" "000" "id1"
"2017-03-29 15:00:00" "000" "id1"
"2017-03-29 16:00:00" "222" "id1"
"2017-03-29 17:00:00" "000" "id1"
"2017-03-29 18:00:00" "000" "id1"
"2017-03-29 19:00:00" "000" "id1"
"2017-03-29 20:00:00" "000" "id1"
"2017-03-29 21:00:00" "000" "id1"
"2017-03-29 22:00:00" "000" "id1"
"2017-03-29 23:00:00" "123" "id1"
"2017-03-30 00:00:00" "000" "id1"
"2017-03-30 01:00:00" "000" "id1"
"2017-03-30 02:00:00" "000" "id1"
"2017-03-30 03:00:00" "000" "id1"
"2017-03-30 04:00:00" "000" "id1"
"2017-03-30 05:00:00" "000" "id1"
"2017-03-30 07:00:00" "000" "id1"
"2017-03-30 08:00:00" "000" "id1"
"2017-03-30 09:00:00" "000" "id1"
"2017-03-30 10:00:00" "333" "id1"
"2017-03-30 11:00:00" "000" "id1"
"2017-03-30 12:00:00" "123" "id1"
"2017-03-30 13:00:00" "000" "id1"
"2017-03-30 14:00:00" "000" "id1"
"2017-03-30 15:00:00" "000" "id1"
"2017-03-30 16:00:00" "000" "id1"
"2017-03-30 18:00:00" "000" "id1"
"2017-03-30 19:00:00" "000" "id1"
"2017-03-30 20:00:00" "000" "id1"
"2017-03-30 21:00:00" "000" "id1"
"2017-03-30 22:00:00" "000" "id1"
"2017-03-30 23:00:00" "000" "id1"
"2017-03-31 00:00:00" "000" "id1"
"2017-03-31 01:00:00" "000" "id1"
"2017-03-31 02:00:00" "000" "id1"
"2017-03-31 03:00:00" "000" "id1"
"2017-03-31 04:00:00" "000" "id1"
"2017-03-31 05:00:00" "000" "id1"
"2017-03-31 06:00:00" "000" "id1"
"2017-03-31 07:00:00" "000" "id1"
"2017-03-31 08:00:00" "000" "id1"
"2017-03-31 09:00:00" "000" "id1"
"2017-03-29 12:00:00" "756" "id2"
"2017-03-29 13:00:00" "701" "id2"
"2017-03-29 14:00:00" "758" "id2"
"2017-03-29 15:00:00" "867" "id2"
"2017-03-29 16:00:00" "924" "id2"
"2017-03-29 17:00:00" "871" "id2"
"2017-03-29 18:00:00" "817" "id2"
"2017-03-29 19:00:00" "109" "id2"
"2017-03-29 20:00:00" "131" "id2"
"2017-03-29 21:00:00" "126" "id2"
"2017-03-29 22:00:00" "120" "id2"
"2017-03-29 23:00:00" "120" "id2"
"2017-03-30 00:00:00" "930" "id2"
"2017-03-30 01:00:00" "108" "id2"
"2017-03-30 09:00:00" "867" "id2"
"2017-03-30 10:00:00" "808" "id2"
"2017-03-30 11:00:00" "756" "id2"
"2017-03-30 12:00:00" "758" "id2"
"2017-03-30 13:00:00" "866" "id2"
"2017-03-30 14:00:00" "981" "id2"
"2017-03-30 15:00:00" "981" "id2"
"2017-03-30 16:00:00" "872" "id2"
"2017-03-30 17:00:00" "928" "id2"
"2017-03-30 18:00:00" "109" "id2"
"2017-03-30 19:00:00" "115" "id2"
"2017-03-30 20:00:00" "121" "id2"
"2017-03-30 21:00:00" "131" "id2"
"2017-03-30 22:00:00" "148" "id2"
"2017-03-30 23:00:00" "132" "id2"
"2017-03-31 00:00:00" "115" "id2"
"2017-03-31 01:00:00" "120" "id2"
"2017-03-31 02:00:00" "137" "id2"
"2017-03-31 03:00:00" "137" "id2"
"2017-03-31 04:00:00" "132" "id2"
"2017-03-31 05:00:00" "126" "id2"
"2017-03-31 06:00:00" "126" "id2"
"2017-03-31 07:00:00" "104" "id2"
"2017-03-31 08:00:00" "874" "id2"
"2017-03-31 09:00:00" "651" "id2"
"2017-03-31 10:00:00" "540" "id2"
Result table should be like this:
date_period value_sum id
"2017-03-29 11:00:00 - 2017-03-30 10:00:00" 1365 "id1"
"2017-03-30 11:00:00 - 2017-03-31 10:00:00" 123 "id1"
"2017-03-29 11:00:00 - 2017-03-30 10:00:00" 9013 "id2"
"2017-03-30 11:00:00 - 2017-03-31 10:00:00" 9834 "id2"
Table1 it is just example, the real table has a lot of ids and big dates from 2015 to 2017, not only from 2017-03-29 to 2017-03-31. I need select by all of them sum of values by period from 11:00 to 10:00 of the next day, grouped by ids.
There should be 2 solutions in Oracle and MSSQL.