ROUND SQL function

1. Usage of ROUND to round the unit price.

SQL Server Query 1

            
SELECT  

    ProductID, ProductName, UnitPrice,  

    ROUND(UnitPrice, 2) AS RoundedUnitPrice  

FROM Products  

ORDER BY ProductID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select().Columns("ProductID", "ProductName", "UnitPrice")  

    .Column(new ROUND(new Column("UnitPrice"), 2), "RoundedUnitPrice")  

    .From("Products")  

    .OrderBy(new OrderBy().Set(new Column("ProductID")))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT ProductID,
       ProductName,
       UnitPrice,
       ROUND(UnitPrice, @pMAIN_2507200213501886950, @pMAIN_2507200213501886951) AS RoundedUnitPrice
FROM Products
ORDER BY ProductID ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of ROUND to round the total order cost.

SQL Server Query 2

            
SELECT  

    od.OrderID,  

    SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS OrderTotal,  

    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS RoundedOrderTotal  

FROM [Order Details] od  

GROUP BY od.OrderID  

ORDER BY od.OrderID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select().Column("od.OrderID", "OrderID")  

    .Column(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice")  

        .MULTIPLY().StartBracket(1).SUBTRACT("od.Discount").EndBracket()), "OrderTotal")  

    .Column(new ROUND(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice")  

        .MULTIPLY().StartBracket(1).SUBTRACT("od.Discount").EndBracket()), 2), "RoundedOrderTotal")  

    .From("[Order Details]", "od")  

    .GroupBy(new GroupBy(new Column("od.OrderID")))  

    .OrderBy(new OrderBy().SetColumnAscending("od.OrderID"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT od.OrderID AS OrderID,
       SUM(od.Quantity * od.UnitPrice * (@pMAIN_2507200213501946740 - od.Discount)) AS OrderTotal,
       ROUND(SUM(od.Quantity * od.UnitPrice * (@pMAIN_2507200213501946741 - od.Discount)), @pMAIN_2507200213501946742, @pMAIN_2507200213501946743) AS RoundedOrderTotal
FROM [Order Details] AS od
GROUP BY od.OrderID
ORDER BY od.OrderID ASC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of ROUND to round the average price.

SQL Server Query 3

            
SELECT  

    c.CategoryID, c.CategoryName,  

    ROUND(Subquery.AvgPrice, 2) AS RoundedAveragePrice  

FROM Categories c  

JOIN (  

    SELECT  

        CategoryID,  

        AVG(UnitPrice) AS AvgPrice  

    FROM Products  

    GROUP BY CategoryID  

) AS Subquery ON c.CategoryID = Subquery.CategoryID  

ORDER BY c.CategoryID;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select().Columns("c.CategoryID", "c.CategoryName")  

    .Column(new ROUND(new Column("Subquery.AvgPrice"), 2), "RoundedAveragePrice")  

    .From("Categories", "c")  

    .Join(new List<IJoin>()  

    {  

        new Join(new SqlQueryBuilder().Select()  

            .Column("CategoryID", "CategoryID")  

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

            .From("Products")  

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

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

    })  

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

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT c.CategoryID,
       c.CategoryName,
       ROUND(Subquery.AvgPrice, @pMAIN_2507200213502086140, @pMAIN_2507200213502086141) AS RoundedAveragePrice
FROM Categories AS c
     INNER JOIN
     (SELECT CategoryID AS CategoryID,
             AVG(UnitPrice) AS AvgPrice
      FROM Products
      GROUP BY CategoryID) AS Subquery
     ON c.CategoryID = Subquery.CategoryID
ORDER BY c.CategoryID ASC;


            
        

Parameters (If used)

Query Results 3: