VAR SQL function

1. Usage of VAR to show order amount variance by customers

SQL Server Query 1

            
SELECT  

    o.CustomerID,  

    c.CompanyName,  

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

    VAR(od.UnitPrice * od.Quantity) AS OrderAmountVariance  

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 OrderAmountVariance 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 VAR(new ColumnArithmatic("od.UnitPrice").MULTIPLY("od.Quantity")), "OrderAmountVariance")  

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

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT o.CustomerID AS CustomerID,
       c.CompanyName AS CompanyName,
       AVG(od.UnitPrice * od.Quantity) AS AverageOrderAmount,
       VAR(od.UnitPrice * od.Quantity) AS OrderAmountVariance
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 OrderAmountVariance DESC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of VAR to see price variance across product categories

SQL Server Query 2

            
SELECT  

    p.CategoryID,  

    c.CategoryName,  

    AVG(p.UnitPrice) AS AveragePrice,  

    VAR(p.UnitPrice) AS PriceVariance  

FROM Products AS p  

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

GROUP BY p.CategoryID, c.CategoryName  

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

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

    .Build();  

            
        

Query build by SqlQueryBuilder 2

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


            
        

Parameters (If used)

Query Results 2:

3. Usage of VAR for variance of price within categories

SQL Server Query 3

            
SELECT  

    p.ProductID,  

    p.ProductName,  

    p.CategoryID,  

    c.CategoryName,  

    p.UnitPrice,  

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

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

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