Database Access with Visual Basic .NET (3rd Edition)

You can perform calculations on fields in a query. To do so, simply replace the name of a field in a SELECT clause with the name of an arithmetic expression. For example, say that you want to create a query to calculate the sales tax on each item in your inventory (as stored in the table tblItem). The following SQL query calculates a 7.5 percent sales tax for each piece of merchandise in tblItem:

SELECT ID, Name, Price, Price * 0.075 AS SalesTax FROM dbo.tblItem

This query produces the following result.

Because you're dealing with money here, you may need to round the result to two digits to the right of the decimal. Fortunately, SQL Server has a ROUND function that enables you to do so easily. The most commonly used form of ROUND takes two parameters, a decimal value and an integer that specifies how many digits to the right of the decimal you want. The query

SELECT Name, Retail Price, ROUND (Retail Price + Retail Price * 0.075, 2) AS PriceWithTax FROM dbo.tblInventory

produces the following result.