STDEV SQL function

1. Usage of STDEV to see standard deviation of orders

SQL Server Query 1

            
SELECT  

    o.CustomerID,  

    c.CompanyName,  

    AVG(od.UnitPrice * od.Quantity) AS AverageOrderAmount,  

    STDEV(od.UnitPrice * od.Quantity) AS OrderAmountStandardDeviation  

FROM Orders AS o  

JOIN Customers AS c ON o.CustomerID = c.CustomerID  

JOIN [Order Details] AS od ON o.OrderID = od.OrderID  

GROUP BY o.CustomerID, c.CompanyName  

ORDER BY OrderAmountStandardDeviation DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Column("o.CustomerID", "CustomerID")  

    .Column("c.CompanyName", "CompanyName")  

    .Column(new AVG(new ColumnArithmatic("od.UnitPrice").MULTIPLY("od.Quantity")), "AverageOrderAmount")  

    .Column(new STDEV(new ColumnArithmatic("od.UnitPrice").MULTIPLY("od.Quantity")), "OrderAmountStandardDeviation")  

    .From("Orders", "o")  

    .Join(new List<IJoin>()  

    {  

        new INNERJOIN().TableName(new Table("Customers", "c"))  

            .On(new Column("o.CustomerID").Equale(new Column("c.CustomerID"))),  

        new INNERJOIN().TableName(new Table("[Order Details]", "od"))  

            .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

    })  

    .GroupBy(new GroupBy("o.CustomerID", "c.CompanyName"))  

    .OrderBy(new OrderBy().SetColumnDescending("OrderAmountStandardDeviation"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT o.CustomerID AS CustomerID,
       c.CompanyName AS CompanyName,
       AVG(od.UnitPrice * od.Quantity) AS AverageOrderAmount,
       STDEV(od.UnitPrice * od.Quantity) AS OrderAmountStandardDeviation
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
GROUP BY o.CustomerID, c.CompanyName
ORDER BY OrderAmountStandardDeviation DESC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of STDEV to find standard deviation across product categories

SQL Server Query 2

            
SELECT  

    p.CategoryID,  

    c.CategoryName,  

    AVG(p.UnitPrice) AS AveragePrice,  

    STDEV(p.UnitPrice) AS PriceStandardDeviation  

FROM Products AS p  

JOIN Categories AS c ON p.CategoryID = c.CategoryID  

GROUP BY p.CategoryID, c.CategoryName  

ORDER BY PriceStandardDeviation DESC;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Column("p.CategoryID", "CategoryID")  

    .Column("c.CategoryName", "CategoryName")  

    .Column(new AVG(new Column("p.UnitPrice")), "AveragePrice")  

    .Column(new STDEV(new Column("p.UnitPrice")), "PriceStandardDeviation")  

    .From("Products", "p")  

    .Join(new List<IJoin>()  

    {  

        new INNERJOIN().TableName(new Table("Categories", "c"))  

            .On(new Column("p.CategoryID").Equale(new Column("c.CategoryID")))  

    })  

    .GroupBy(new GroupBy("p.CategoryID", "c.CategoryName"))  

    .OrderBy(new OrderBy().SetColumnDescending("PriceStandardDeviation"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT p.CategoryID AS CategoryID,
       c.CategoryName AS CategoryName,
       AVG(p.UnitPrice) AS AveragePrice,
       STDEV(p.UnitPrice) AS PriceStandardDeviation
FROM Products AS p
     INNER JOIN
     Categories AS c
     ON p.CategoryID = c.CategoryID
GROUP BY p.CategoryID, c.CategoryName
ORDER BY PriceStandardDeviation DESC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of STDEV to compare the price of each products

SQL Server Query 3

            
SELECT  

    p.ProductID,  

    p.ProductName,  

    p.CategoryID,  

    c.CategoryName,  

    p.UnitPrice,  

    STDEV(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS CategoryPriceStandardDeviation,  

    (p.UnitPrice - AVG(p.UnitPrice) OVER (PARTITION BY p.CategoryID)) / STDEV(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS ZScore  

FROM Products AS p  

JOIN Categories AS c ON p.CategoryID = c.CategoryID  

ORDER BY p.CategoryID, p.UnitPrice;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Columns("p.ProductID", "p.ProductName", "p.CategoryID", "c.CategoryName", "p.UnitPrice")  

    .Column(new STDEV(new Column("p.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("p.CategoryID"))), "CategoryPriceStandardDeviation")  

    .Column(new ColumnArithmatic().StartBracket("p.UnitPrice").SUBTRACT(new AVG(new Column("p.UnitPrice"))  

        .OVER(new OVER().PARTITION_BY(new Column("p.CategoryID"))))  

        .EndBracket().DIVIDE(new STDEV(new Column("p.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("p.CategoryID")))), "ZScore")  

    .From("Products", "p")  

    .Join(new List<IJoin>()  

    {  

        new INNERJOIN().TableName(new Table("Categories", "c"))  

            .On(new Column("p.CategoryID").Equale(new Column("c.CategoryID")))  

    })  

    .OrderBy(new OrderBy().SetColumnAscending("p.CategoryID")  

        .SetColumnAscending("p.UnitPrice"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT p.ProductID,
       p.ProductName,
       p.CategoryID,
       c.CategoryName,
       p.UnitPrice,
       STDEV(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS CategoryPriceStandardDeviation,
       (p.UnitPrice - AVG(p.UnitPrice) OVER (PARTITION BY p.CategoryID)) / STDEV(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS ZScore
FROM Products AS p
     INNER JOIN
     Categories AS c
     ON p.CategoryID = c.CategoryID
ORDER BY p.CategoryID ASC, p.UnitPrice ASC;


            
        

Parameters (If used)

Query Results 3: