15,846,346 members
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

## Solution 2

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.

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.

## Solution 1

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.

v2