sql query to get running Average,Minimum,Maximum,Total,standard deviation, count for every 5 minutes

Question

I have a table with time (column) and power (column). I want to calculate different result sets on following table data:

My Table:

[timestamp]                         [power]

2016-01-06 13:32:27                    1
2016-01-06 13:33:27                    2
2016-01-06 13:34:27                    3
2016-01-06 13:35:27                    4
2016-01-06 13:36:27                    5
2016-01-06 13:37:27                    6
2016-01-06 13:38:27                    7
2016-01-06 13:39:27                    8
2016-01-06 13:40:27                    9
2016-01-06 13:41:27                    10

Total as:

 [timestamp]                         [power]              Total

2016-01-06 13:32:27                    1                    1
2016-01-06 13:33:27                    2                    3
2016-01-06 13:34:27                    3                    6
2016-01-06 13:35:27                    4                    10
2016-01-06 13:36:27                    5                    15
2016-01-06 13:37:27                    6                    21
2016-01-06 13:38:27                    7                    28
2016-01-06 13:39:27                    8                    36
2016-01-06 13:40:27                    9                    45
2016-01-06 13:41:27                    10                   55

Total for every 5 minute as:

[timestamp]                         [power]              Total_every_5minutes

--2016-01-06 13:32:27                    1                    1
--2016-01-06 13:33:27                    2                    3
--2016-01-06 13:34:27                    3                    6
--2016-01-06 13:35:27                    4                    10
2016-01-06 13:36:27                    5                    15
--2016-01-06 13:37:27                    6                    21
--2016-01-06 13:38:27                    7                    28
--2016-01-06 13:39:27                    8                    36
2016-01-06 13:40:27                    9                    45

Similarly I want to calculate for average, minimum, maximum, standard deviation, & count. As of now I have written a query which will add a column with,

Please note: That I should not write stored procedure. As I have a windows application where user selects database and it will list the tables, from the listed tables user selects the columns and on a particular column he will set to calculate total or etc.. Upon selection the query is building. and the query will execute and bind the results to datagridview.

IF OBJECT_ID('tempdb..#mytemptable') IS NOT NULL DROP TABLE #mytemptable;
SELECT  SUM( CAST ([power] AS bigint)  ) AS power_TOTAL  INTO #MYTEMPTABLE 
  FROM ( SELECT t1.[timestamp], t1.[power] FROM [REPORT] t1 ) TEMPTABLE
SELECT t1.[timestamp], t1.[power], RM_temp.* FROM [REPORT] t1 , #MYTEMPTABLE RM_temp;

I mean if you can create SQLFiddle it would be easy to answer. And instead of inserting to another table if just desired output is achieved. — Siraj ul Haq, May 18 '16 at 09:19
@mohan111 a lot of data will be pushed to sql server by scada software. my application will generate reports which will be different for different sites for example my client want to view the records for 01/01/2016 01:00:00 AM to 01/01/2016 02:00:00 AM the records may vary from site to site depending on how scada is configured to push the data to server. scada may be pushing 10 records for minute of which all are not important. they have some caluclations which should not exceedd...so on and so forth — Tan, May 18 '16 at 09:43
@SirajulHaq i will try to create sqlfiddle had never done it before though i ll try. thank you — Tan, May 18 '16 at 09:45

score 0 · Answer 1 · answered May 18 '16 at 09:52

may be this will work for you based on your data

DECLARE @Table1  TABLE 
    ( timestamp  varchar(19),  power  int)
;

INSERT INTO @Table1
    ( timestamp ,  power )
VALUES
    ('2016-01-06 13:32:27', 1),
    ('2016-01-06 13:33:27', 2),
    ('2016-01-06 13:34:27', 3),
    ('2016-01-06 13:35:27', 4),
    ('2016-01-06 13:36:27', 5),
    ('2016-01-06 13:37:27', 6),
    ('2016-01-06 13:38:27', 7),
    ('2016-01-06 13:39:27', 8),
    ('2016-01-06 13:40:27', 9),
    ('2016-01-06 13:41:27', 10)
;


Select [timestamp],[power] from (
SELECT timestamp, power , RunningTotal = power + COALESCE(
(
  SELECT SUM(power)
    FROM @Table1 AS i
    WHERE i.timestamp < o.timestamp), 0
)
FROM @Table1 AS o)TT where  exists (Select * from (select power * 5 ID from @Table1) T WHERE T.id = TT.RunningTotal )
ORDER BY timestamp;

sql query to get running Average,Minimum,Maximum,Total,standard deviation, count for every 5 minutes

1 Answers1