Other SQUARE SQL function

1. Usage of SQUARE to show Price Variance Analyzing

SQL Server Query 1

            
 WITH CategoryAvgPrices AS (  

     SELECT  

         CategoryID,  

         AVG(UnitPrice) AS AvgCategoryPrice  

     FROM Products  

     GROUP BY CategoryID  

 )  

 SELECT  

     p.ProductID,  

     p.ProductName,  

     p.CategoryID,  

     c.CategoryName,  

     p.UnitPrice,  

     cap.AvgCategoryPrice,  

     SQUARE(p.UnitPrice - cap.AvgCategoryPrice) AS SquaredPriceDifference  

 FROM Products p  

 JOIN CategoryAvgPrices cap ON p.CategoryID = cap.CategoryID  

 JOIN Categories c ON p.CategoryID = c.CategoryID  

 ORDER BY p.CategoryID, p.ProductID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CategoryAvgPrices"), new SqlQueryBuilder()  

         .Select().Column("CategoryID", "CategoryID")  

         .Column(new AVG(new Column("UnitPrice")), "AvgCategoryPrice")  

         .From("Products")  

         .GroupBy(new GroupBy(new Column("CategoryID")))  

     )  

     .Select()  

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

     .Column(new SQUARE(new ColumnArithmatic("p.UnitPrice").SUBTRACT("cap.AvgCategoryPrice")), "SquaredPriceDifference")  

     .From("Products", "p")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("CategoryAvgPrices","cap"))  

             .On(new Column("p.CategoryID").Equale(new Column("cap.CategoryID"))),  

         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.ProductID"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH CategoryAvgPrices
AS (SELECT CategoryID AS CategoryID,
           AVG(UnitPrice) AS AvgCategoryPrice
    FROM Products
    GROUP BY CategoryID)
SELECT p.ProductID,
       p.ProductName,
       p.CategoryID,
       c.CategoryName,
       p.UnitPrice,
       cap.AvgCategoryPrice,
       SQUARE(p.UnitPrice - cap.AvgCategoryPrice) AS SquaredPriceDifference
FROM Products AS p
     INNER JOIN
     CategoryAvgPrices AS cap
     ON p.CategoryID = cap.CategoryID
     INNER JOIN
     Categories AS c
     ON p.CategoryID = c.CategoryID
ORDER BY p.CategoryID ASC, p.ProductID ASC;


            
        

Parameters (If used)

Query Results 1: