How to list sum of values in the specific period

Question

How to list sum of values in the specific period

demokedes

Old Hand

Points: 338
More actions
January 3, 2023 at 11:01 am

#4132982
Sorry for very newbie questions. I have a table, shown below with values about duration of events. Sometimes I have several events in one hour/day. Instead of showing each particular event, I would like ot have sum of duration of all events on hourly/daily basis. How to achieve that?
```
SELECT *
FROM [dbo].[switches]
WHERE previousevent='on' 
AND friendlyname='BathroomLight'
ORDER BY timestamp DESC;
```
- This topic was modified 1 year, 10 months ago by demokedes.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

Have a look into the DATEPART function. That should point you in the right direction.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

We will need your help in order to be able to help you, so please help us!

😎

It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

demokedes Old Hand Points: 338 More actions · Answer 3

Eirikur,

You're right. I will try to be more concrete.

Script for generating the data:

drop table #Test

create table #Test
(
    datum datetime,
    duration int,
)
insert into #Test (datum, duration)
VALUES
('2023-01-03 16:07:31.000', 14),
('2023-01-04 12:16:32.000', 15),
('2023-01-04 11:07:31.000', 16),
('2023-01-03 10:05:31.000', 17)

This will generate table:

datum duration

2023-01-03 16:07:31.000 14

2023-01-04 12:16:32.000 15

2023-01-04 11:07:31.000 16

2023-01-03 10:05:31.000 17

I would like to get output like that:

Date SumDuration

2023-01-03 31

2023-01-04 31

I have to emphasize, I am doing this in "time-series" Grafana report. If I use traditional "table" approach, I already sucesfuly created query:

SELECT CONVERT(VARCHAR(10), [timestamp], 111), SUM(duration) Duration
FROM [dbo].[switches]
WHERE friendlyName='BathroomLight'
GROUP BY CONVERT(VARCHAR(10), [timestamp], 111)
ORDER BY CONVERT(VARCHAR(10), [timestamp], 111);