Calculate Sum of Mean and Std dev in sql query on single column

Question

I am having table name as "Table1" in mysql.I have to find Sum of Mean and Std dev on column "Open".I did it easily using python but I am unable to do it using sql.

Select * from BANKNIFTY_cal_spread;

Date                 Current  Next       difference
2021-09-03 00:00:00 36914.8  37043.95    129.14999999999418
2021-09-06 00:00:00 36734    36869.15    135.15000000000146
2021-09-07 00:00:00 36572.9  36710.65    137.75
2021-09-08 00:00:00 36945    37065       120
2021-09-09 00:00:00 36770    36895.1     125.09999999999854

Python Code-

nf_fut_mean = round(df['difference'].mean())
print(f"NF Future Mean: {nf_fut_mean}")
nf_fut_std = round(df['difference'].std())
print(f"NF Future Standard Deviation: {nf_fut_std}")

upper_range = round((nf_fut_mean + nf_fut_std))
lower_range = round((nf_fut_mean - nf_fut_std))

I search for Sql solution but I didn't get it. I tried building query but it's not showing correct results in query builder in grafana alerting.

Now I added Mean column ,std dev column , upper_range and lower_range column using python dataframe and pushed to mysql table.

@Booboo, After removing Date from SQL Query, it's showing correct results in two columns- average + std_deviation and average - std_deviation.

select average + std_deviation, average - std_deviation from (
    select avg(difference) as average, stddev_pop(difference) as std_deviation from BANKNIFTY_cal_spread
) sq

score 1 · Answer 1 · answered Jun 24 '22 at 22:34

It looks as though the sample you're using for the aggregations for MEAN, STDDEV, etc is the entire table - in which case you have to drop the DATE field from the query's result set.

You could also establish the baseline query using a CTE (Common Table Expression) using a WITH statement instead of a subquery, and then apply the subsequent processing:

WITH BN_CTE AS 
(
select avg(difference) as average, stddev_pop(difference) as std_deviation from BANKNIFTY_cal_spread
)

select average + std_deviation, average - std_deviation from BN_CTE;

Booboo · Answer 2 · 2022-06-24T10:50:02.457

With the data you posted having only a single Open column value for any given Date column value, you standard deviation should be 0 (and the average just that single value).

I am having difficulty in understanding your SQL since I cannot see how it relates to finding the sum (and presumably the difference, which you also seem to want) of the average and standard deviation of column Open in table Table1. If I just go by your English-language description of what you are trying to do and your definition of table Table1, then the following should work. Note that since we want both the sum and difference of two values, which are not trivial to calculate, we should calculate those two values only once:

select Date, average + std_deviation, average - std_deviation from (
    select Date, avg(Open) as average, stddev_pop(Open) as std_deviation from Table1
    group by Date
) sq
order by Date

Note that I am using column aliases in the subquery that do not conflict with built-in MySQL function names.

I ran my code against one of my own tables and it works. You should instead of posting worthless images instead go to a website such as db-fiddle.com and create the *actual* table with actual data for people (such as myself) to test against. Putting a bounty on a question doesn't excuse you from specifying your question accurately the first time and providing such a table. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query). — Booboo, Jun 24 '22 at 11:29

score 0 · Answer 3 · answered Jun 24 '22 at 14:18

SQL does not allow both calculating something in the SELECT clause and using it. (Yes, @variables allow in limited cases; but that won't work for aggregates in the way hinted in the Question.)

Either repeat the expressions:

SELECT average(difference) AS mean,
       average(difference) + stddev_pop(difference) AS "mean-sigma",
       average(difference) - stddev_pop(difference) AS "mean+sigma"
    FROM BANKNIFTY_cal_spread;

Or use a subquery to call the functions only once:

SELECT mean, mean-sigma, mean+sigma
    FROM (  SELECT
                average(difference)    AS mean,
                stddev_pop(difference) AS sigma
            FROM BANKNIFTY_cal_spread
         ) AS x;

I expect the timings to be similar.

And, as already mentioned, avoid using aliases that are identical to function names, etc.

Calculate Sum of Mean and Std dev in sql query on single column

3 Answers3