CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Reyaz
Reyaz

Reputation: 55

Standard Deviation of the column with day wise

I need a query to find the standard deviation for a column with daywise. My table has some 30,000 rows with the timestamp of every 10 mins. Please find the below sample data with columns timestamp and TF. i need to get the deviation for rest of the columns as well

Timestamp                  TF
2012-05-17 19:15:35.000    10
2012-05-17 19:25:35.000    10
2012-05-17 19:35:35.000    10
2012-05-17 19:45:35.000    10
2012-05-17 19:55:35.000    10
2012-05-17 20:05:35.000    10
2012-05-17 20:15:35.000    10
2012-05-17 20:26:46.000    10
2012-05-18 00:06:48.000    10
2012-05-18 00:16:48.000    10
2012-05-18 00:26:48.000    10
2012-05-18 00:36:48.000    10
2012-05-18 00:46:48.000    10
2012-05-18 00:56:48.000    10
2012-05-18 01:06:48.000    10
2012-05-18 01:16:48.000    10
2012-05-18 01:26:48.000    10
2012-05-18 01:36:49.000    10

The need output as

Timestamp        TF
2012-05-17     0
2012-05-18       0
---             --
---             --
---             --
---             --

Please help!! Thanks in advance!!

Any ideas?

Upvotes: 1

Views: 2882

Answers (1)

zebediah49
zebediah49

Reputation: 7611

EDIT: I transposed DAY and DATE: DAY returns day-of-month; DATE returns the time truncated to the day. Answer is updated to include that fix.

You will need to use an aggregate function, grouping by the day:

SELECT DATE(Timestamp), STDDEV(TV) FROM MyTable GROUP BY DATE(Timestamp);

I assume there is a DATE() function.

There might not be a STDDEV function. If there isn't, STDDEV(x)=SQRT(AVG((x-AVG(x))^2), which might have to be implemented as a joined subquery:

SELECT DATE(Timestamp), SQRT(AVG((a.TF-b.mean)*(a.TF-b.mean)) FROM MyTable a LEFT JOIN
    (SELECT DATE(Timestamp) day, AVG(TF) mean FROM MyTable GROUP BY DATE(Timestamp)) b
        ON DATE(a.Timestamp)=b.day 
    GROUP BY b.day;

Upvotes: 1

Related Questions