How to list sum of values in the specific period

  • 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 2 months, 3 weeks ago by  demokedes.
  • 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

  • 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.

  • 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)
    ('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);

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

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