Get an average of values in seconds, grouped as values in minutes

Question

5.00/5 (1 vote)

See more:

I have a sql table having three columns: date, time, and value. Values are in any time and there are many values during the same minute. I would need to get out just values for each minute, and those should be an average of all values during the same minute.

If the data in the table is:

SQL

myDate		myTime		myValue
2012.01.12	10:51:33	100
2012.01.12	10:51:45	200
2012.01.12	10:52:28	300
2012.01.12	10:52:41	200
2012.01.12	10:52:58	100

the output should be something like:

SQL

myDate		myTime		myValue
2012.01.12	10:51:00	150
2012.01.12	10:52:00	200

Any proposals of what kind of sql would result in this?
Thanks for any help.

Posted 13-Jan-12 1:40am

luotope

Add a Solution

Comments

Om Prakash Pant 13-Jan-12 7:44am

what are the datatypes of each column?

3 solutions

Solution 3

Try this

SQL

DECLARE @timedata as table (mydate datetime , mytime datetime , myvalues bigint)
DECLARE @timedata_Formated as table (mydate datetime , mytime NVARCHAR(255) , myvalues bigint)

insert into @timedata select '2012.01.12','10:51:33.000',100
insert into @timedata select '2012.01.12','10:51:45.000',200
insert into @timedata select '2012.01.12','10:52:28.000',300
insert into @timedata select '2012.01.12','10:52:41.000',200
insert into @timedata select '2012.01.12','10:52:58.000',100

INSERT INTO @timedata_Formated
SELECT mydate,CAST(DATEPART(hh,mytime) AS NVARCHAR(50)) + ':' + CAST(DATEPART(mi,mytime) AS NVARCHAR(50)) + ':00',myvalues   FROM @timedata

SELECT mydate,mytime,AVG(myvalues) FROM @timedata_Formated Group by mydate,mytime

Posted 13-Jan-12 2:06am

RDBurmon

Comments

Petri Luoto 13-Jan-12 14:49pm

Thanks! Processing of 780.000 lines resulting in 268.000 lines took 14 seconds. Quite nice. But there was a problem: the time column went funny after 59 minutes:

2012-01-06 00:00:00.000 9:1:00 3581
2012-01-06 00:00:00.000 9:0:00 3222
2012-01-06 00:00:00.000 8:9:00 1116
2012-01-06 00:00:00.000 8:8:00 944
2012-01-06 00:00:00.000 8:7:00 960
2012-01-06 00:00:00.000 8:6:00 960
2012-01-06 00:00:00.000 8:59:00 3268
2012-01-06 00:00:00.000 8:58:00 3314
2012-01-06 00:00:00.000 8:57:00 3254

Solution 1

this may not be the cleaner & optimum way but you can acheive this by following query:

SQL

select myDate, left(myTime,2) + ':' + substring(myTime, 4,2) + ':00' , sum(cast(right(myTime,2) as int)), avg(myValue) from table_data group by myDate, left(myTime,2), substring(myTime, 4,2)

Posted 13-Jan-12 2:01am

Om Prakash Pant

Comments

Petri Luoto 13-Jan-12 14:30pm

Thanks! I tried this, but for some reason got an error message: "Argument data type time is invalid for argument 1 of substring function."
I did not investigate it closer, but perhaps something with datatypes. Those were Date, Time(7), and int.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Costica U · Accepted Answer · 2012-01-13T02:02:00

Try this

SQL

if object_id('tempdb..#temp') is not null
	begin
	drop table #temp
	end

create table #temp
	(
	myDate varchar(10),
	myTime varchar(8),
	myValue int
	)

insert into #temp values('2012.01.12',	'10:51:33',	100)
insert into #temp values('2012.01.12',	'10:51:45',	200)
insert into #temp values('2012.01.12',	'10:52:28',	300)
insert into #temp values('2012.01.12',	'10:52:41',	200)
insert into #temp values('2012.01.12',	'10:52:58',	100)

select	myDate,
	myTime,
	avg(myValue) as myValue
from	(
	select	myDate,
		left(myTime,5)+':00' as myTime,
		myValue
	from	#temp
	) a
group
by	myDate,
	myTime

Get an average of values in seconds, grouped as values in minutes

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us