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?
SELECT *
FROM [dbo].[switches]
WHERE
previousevent='on' AND friendlyname='BathroomLight'
ORDER BY timestamp DESC;
sum(duration)
group by
harishgg,
Sample 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)
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
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.
Robert,
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.
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:
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.
Robert,
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')