CODEWITHSUNDEEP
sqlsql-servergroupingaggregate-functions
CrudeOperator
CrudeOperator

Reputation: 111

SQL Query to Get Standard Deviation of Grouped Totals

I've been trying to find a SQL query that first groups and sums total revenue by employee, like this below: 

**SELECT Employee_Name, 
         SUM(Total_Revenue)
    FROM TABLE table_Name
GROUP BY Employee_Name**

Then I also want the query to then find the standard deviation of the aggregated group data. Something like this below:

**SELECT Rep_Name, 
         STDEV(Total_Revenue)
    FROM  Sales
Group By Rep_Name**

All attempts thus far haven't worked and I could really do with some guidance from someone more seasoned in SQL. I've tried what seems an infinite amount of combinations of the above two queries but there's nothing close.

I've managed to get the individual standard deviation of each employee total revenue, and I've also managed to get the standard deviation based on the total revenue of each sale.

I essentially want to compare the total revenue of each employee and establish the standard deviation of just the total revenue of each employee.

Below is what I produced with excel formulas and it's ultimately what I want the SQL output to produce so I can run further analyses. 

   ID   Total   Average   Standard Deviation 
    1   3100.00 1787.92   800.53
    2   3102.30 1787.92   800.53
    3   2363.04 1787.92   800.53
    4   2000.00 1787.92   800.53
    5   1749.87 1787.92   800.53
    6   1641.43 1787.92   800.53
    7   1387.77 1787.92   800.53
    8   1299.25 1787.92   800.53
    9   1283.61 1787.92   800.53
    10  1203.11 1787.92   800.53
    11  536.75  1787.92   800.53

Any help is warmly welcomed.

All the best, :)

Upvotes: 2

Views: 3017

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you want a window function:

SELECT Rep_Name,
       AVG(SUM(Total_Revenue)) OVER () as avg_rep, 
       STDEV(SUM(Total_Revenue)) OVER () as stdev_rep
FROM Sales
GROUP BY Rep_Name;

This does the aggregation and then calculates the standard deviation on the sums.

Upvotes: 2

Related Questions