Click here to Skip to main content
15,850,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a solution which stores current energy consumption values (kW) to sql table several times in one minute. The application is running 24/7.

I would need to show graphics based on each minute average consumption, so first I need to get an average of values within each minute. Finally the result should be an array of 1440 (=60*24) values, corresponding each minute in one day.

My question is, can I get this out of using SQL statement, or should I just retrieve all values during the day, and process the data separately in C#, and create the array based on calculations?

As an example, I'll get an average of all values in a minute with this statement:
SELECT AVG (KL_Energy) FROM [HomeControl].[dbo].[Home] where Day= '2012-01-04' and Time >= '21:24' and Time < '21:25'


I wouldn't like to repeat this 1440 times for a day :)

Thanks for any help.
Posted

SQL
SELECT Day, DATEPART(min, Time), AVG(KL_Energy) FROM Home GROUP BY Day, DATEPART(min, Time) HAVING Day='...'
It might take some time for big table. Give it a try, I cannot try it now.
 
Share this answer
 
v2
Thanks for your quick response!

I changed min to mi, and it actually gives me an array of values!! Thanks!

What I would still need to improve is to include the actual Time value to the list. Now it is like:
2012-01-04	0	3126
2012-01-04	1	3070
2012-01-04	2	3111
...

not showing the minute of the avg value calculated. This wouldn't be needed if there allways is really all values, but sometimes, due to, say windows boot up, there will be no values for certain minutes.

Is it easy to include another column for Time?

Thanks again.
 
Share this answer
 
Comments
Petri Luoto 12-Jan-12 6:53am    
Taking a closer look to result, it allways returns an array of 60 values. But actually it should return an array with 1440 values. In the table there are some 7300 values per day, which is around 5 values per minute. Those are the values which should be calculated for one average value, total of 1440 from the whole Table.

Perhaps you figure out another proposal, and with Time value included? Thanks in advance.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900