STDEVP SQL function

1. Usage of STDEV to see standard deviation of orders

SQL Server Query 1

            
SELECT  

    c.Country,  

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

    STDEVP(od.UnitPrice * od.Quantity) AS OrderAmountPopulationStandardDeviation  

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 c.Country  

ORDER BY OrderAmountPopulationStandardDeviation DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Column("c.Country", "Country")  

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

    .Column(new STDEVP(new ColumnArithmatic("od.UnitPrice").MULTIPLY("od.Quantity")), "OrderAmountPopulationStandardDeviation")  

    .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(new Column("c.Country")))  

    .OrderBy(new OrderBy().SetColumnDescending("OrderAmountPopulationStandardDeviation"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.Country AS Country,
       AVG(od.UnitPrice * od.Quantity) AS AverageOrderAmount,
       STDEVP(od.UnitPrice * od.Quantity) AS OrderAmountPopulationStandardDeviation
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 c.Country
ORDER BY OrderAmountPopulationStandardDeviation DESC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of STDEV to see price variation with products

SQL Server Query 2

            
SELECT  

    p.SupplierID,  

    s.CompanyName AS SupplierName,  

    AVG(p.UnitPrice) AS AveragePrice,  

    STDEVP(p.UnitPrice) AS PricePopulationStandardDeviation  

FROM Products AS p  

JOIN Suppliers AS s ON p.SupplierID = s.SupplierID  

GROUP BY p.SupplierID, s.CompanyName  

ORDER BY PricePopulationStandardDeviation DESC;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Column("p.SupplierID", "SupplierID")  

    .Column("s.CompanyName", "SupplierName")  

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

    .Column(new STDEVP(new Column("p.UnitPrice")), "PricePopulationStandardDeviation")  

    .From("Products", "p")  

    .Join(new List<IJoin>()  

    {  

        new INNERJOIN().TableName(new Table("Suppliers", "s"))  

            .On(new Column("p.SupplierID").Equale(new Column("s.SupplierID")))  

    })  

    .GroupBy(new GroupBy("p.SupplierID", "s.CompanyName"))  

    .OrderBy(new OrderBy().SetColumnDescending("PricePopulationStandardDeviation"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT p.SupplierID AS SupplierID,
       s.CompanyName AS SupplierName,
       AVG(p.UnitPrice) AS AveragePrice,
       STDEVP(p.UnitPrice) AS PricePopulationStandardDeviation
FROM Products AS p
     INNER JOIN
     Suppliers AS s
     ON p.SupplierID = s.SupplierID
GROUP BY p.SupplierID, s.CompanyName
ORDER BY PricePopulationStandardDeviation DESC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of STDEV for relative price variability

SQL Server Query 3

            
SELECT  

    p.ProductID,  

    p.ProductName,  

    p.CategoryID,  

    c.CategoryName,  

    p.UnitPrice,  

    STDEVP(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS CategoryPricePopulationStandardDeviation,  

    (p.UnitPrice - AVG(p.UnitPrice) OVER (PARTITION BY p.CategoryID)) / STDEVP(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 STDEVP(new Column("p.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("p.CategoryID")))  

        , "CategoryPricePopulationStandardDeviation")  

    .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 STDEVP(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,
       STDEVP(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS CategoryPricePopulationStandardDeviation,
       (p.UnitPrice - AVG(p.UnitPrice) OVER (PARTITION BY p.CategoryID)) / STDEVP(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: