CODEWITHSUNDEEP
sqlsql-server
Erik Davino
Erik Davino

Reputation: 41

SUM the data from the last update from a group

So, we have a table where we store the predicted noise level for several rooms on an hourly basis, along side the time when the prediction was made.

But this prediction can change several times a day and when this happen we add a new row to the table, to keep history, so we have something like this:

+----+----+-------+--------+------+-------+---------------------+
| room_id | reference_date | hour | noise |     update_time     |
+----+----+-------+--------+------+-------+---------------------+
|    1    |   2019-06-13   |   8  |    0  | 2019-06-13 07:15:23 |
|    1    |   2019-06-13   |   9  |    5  | 2019-06-13 07:15:23 |
|    2    |   2019-06-13   |   8  |    2  | 2019-06-13 07:15:23 |
|    1    |   2019-06-13   |   8  |    5  | 2019-06-13 08:15:23 |
|    1    |   2019-06-13   |   9  |    5  | 2019-06-13 08:15:23 |
+---------+--------+--------+-----+-------+---------------------+

Now we want to SUM the predicted noise level for the whole day, but using only the last update_time, else will have several entries for the same hour of the day, instead of only one.

I've tried several only examples but couldn't getting it working with a single query, we're using SQL Server 14.0.3030.27. I could do it in code, but I believe there is a better solution for that.

The query result should be something like this

+----+----+-------+--------+------+-------+----+
| room_id | reference_date | total_daily_noise |
+----+----+-------+--------+-------------------+
|    1    |   2019-06-13   |        10         |
+---------+--------+-------+-------------------+

Is it possible to do using a single query?

Thank you in advance for the help!

Upvotes: 4

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

row_number() is one method to get the most recent prediction for each hour:

select room_id, reference_date, sum(noise) as noise
from (select t.*,
             row_number() over (partition by room_id, reference_date, hour order by update_time desc) as seqnum
      from t
     ) t
where seqnum = 1
group by room_id;

Another method uses a correlated subquery:

select room_id, reference_date, sum(noise) as noise
from t
where t.update_time = (select max(t2.update_time)
                       from t t2
                       where t2.room_id = t.room_id and
                             t2.reference_date = t.reference_date and
                             t2.hour = t.hour
                      )
group by room_id;

Upvotes: 4

Related Questions