How to list sum of values in the specific period

Dear all,
I have a table, shown below with values about duration of events. Sometimes I have several events in one hour/day. Insted of showin each particular event, I would like ot have sum of duration of all events on hourly/daily basis. How to achieve that?

  FROM [dbo].[switches]
	previousevent='on' AND friendlyname='BathroomLight'
  ORDER BY timestamp DESC;

group by

Provide Sample Data DDL

Sample 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)

I am expecting result something like that:
Date SumDuration

2023-01-03 31

2023-01-04 31

SELECT CAST(datum as date) AS Date, SUM(Duration) AS SumDuration
FROM #Test
1 Like

I get this error:

Column '#Test.datum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I think I found the solution. I needed to add GROUP BY. Now it is working.

SELECT CAST(datum as date) AS Date, SUM(Duration) AS SumDuration
FROM #Test
GROUP BY CAST(datum as date)

Ooops, sorry about that, didn't copy/paste the whole thing.

1 Like

Thank you very much. I have one more question:
-This solution is now giving me sums on daily value. I also managed to get monthly sums with the following code:

SELECT MONTH(CAST(datum as date)) AS Date, SUM(Duration) AS SumDuration
FROM #Test
GROUP BY MONTH(CAST(datum as date))

But what I would like to get now, is hourly sum. WITH "HOUR(CAST(datum as date))" is not working? What should I use to get hourly sums? In my example bellow this is not the best case, since I don't have two duration inside one hour.

SELECT DATEADD(hour, DATEDIFF(hour, '19000101', datum), '19000101') as hour, SUM(Duration) AS SumDuration
FROM #Test
GROUP BY DATEADD(hour, DATEDIFF(hour, '19000101', datum), '19000101')

If you upgrade to SQL Server 2022 then you can use the DATE_BUCKET function:

But until then, the pattern I posted will work, which is to find the DATEDIFF of the date/time fragment from a fixed date, then add that diff to the same fixed date. It essentially rounds the datetime to the fragment you want to group by.

1 Like

Hey Robert,
Since I am using Azure SQL DB, I believe DATE_BUCKET () is working. How can I use it?
I tried with simple: SELECT DATE_BUCKET(hour, 1, @datum); but I believe syntax is incorrect with the last parameter.

One more question on this one:
This monthly sum is working but for name of months I get:

How can I format this in a proper way, for example 2021-01-01-01:00.000? Can I solve this one with DATE_BUCKET() as well?

If you're in Azure DB then yes, DATE_BUCKET will be available. I have no idea why you're getting dates in 1970 if you're using the code I posted. Nothing should have millisecond values.

1 Like

I solved everything, also sum by Month. Before I was using something else for monthly aggregation, but then I used DATEDD/DATEDIFF, you suggested for hourly sum and it is working. Code below.
Thank you very much Robert!

SELECT DATEADD(month, DATEDIFF(month, '19000101', timestamp), '19000101') AS time, SUM(Duration) AS SumDuration
FROM dbo.switches
WHERE friendlyName='KristofMainLight'
GROUP BY DATEADD(month, DATEDIFF(month, '19000101', timestamp), '19000101')
ORDER BY DATEADD(month, DATEDIFF(month, '19000101', timestamp), '19000101')