VARP SQL function

1. Usage of VARP to show standard deviation of population of orders

SQL Server Query 1

            
SELECT  

    c.Country,  

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

    VARP(od.UnitPrice * od.Quantity) AS OrderAmountPopulationVariance  

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 OrderAmountPopulationVariance 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(new Column("od.Quantity"))), "AverageOrderAmount")  

    .Column(new VARP(new ColumnArithmatic("od.UnitPrice").MULTIPLY(new Column("od.Quantity"))), "OrderAmountPopulationVariance")  

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

    .OrderBy(new OrderBy().SetColumnDescending("OrderAmountPopulationVariance"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

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


            
        

Parameters (If used)

Query Results 1:

2. Usage of VARP to see price variance population across product categories

SQL Server Query 2

            
SELECT  

    p.SupplierID,  

    s.CompanyName AS SupplierName,  

    AVG(p.UnitPrice) AS AveragePrice,  

    VARP(p.UnitPrice) AS PricePopulationVariance  

FROM Products AS p  

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

GROUP BY p.SupplierID, s.CompanyName  

ORDER BY PricePopulationVariance 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 VARP(new Column("p.UnitPrice")), "PricePopulationVariance")  

    .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("PricePopulationVariance"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

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


            
        

Parameters (If used)

Query Results 2:

3. Usage of VAR for variance of price within categories population

SQL Server Query 3

            
SELECT  

    p.ProductID,  

    p.ProductName,  

    p.CategoryID,  

    c.CategoryName,  

    p.UnitPrice,  

    VARP(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS CategoryPriceVariance,  

    (p.UnitPrice - AVG(p.UnitPrice) OVER (PARTITION BY p.CategoryID)) / SQRT(VARP(p.UnitPrice) OVER (PARTITION BY p.CategoryID)) AS ZScore  --Approximation  

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 VARP(new Column("p.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("p.CategoryID"))), "CategoryPricePopulationVariance")  

    .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 SQRT(new VARP(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,
       VARP(p.UnitPrice) OVER (PARTITION BY p.CategoryID) AS CategoryPricePopulationVariance,
       (p.UnitPrice - AVG(p.UnitPrice) OVER (PARTITION BY p.CategoryID)) / SQRT(VARP(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: