Performing Calculations in Queries

[ Team LiB ]

Performing Calculations in Queries 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. ID Name Price SalesTax 1 Rubber Chicken 5.99 0.44925 2 Hand Buzzer 1.39 0.10425 3 Stink Bomb 1.29 0.09675 4 Disappearing Penny Magic Trick 3.99 0.29925 5 Invisible Ink 2.29 0.17175 6 Loaded Dice 3.49 0.26175 7 Whoopee Cushion 5.99 0.44925 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. Name Retail Price PriceWithTax Rubber Chicken 5.99 6.44 Hand Buzzer 1.39 1.49 Stink Bomb 1.29 1.39 Disappearing Penny Magic Trick 3.99 4.29 Invisible Ink 2.29 2.46 Loaded Dice 3.49 3.75 Whoopee Cushion 5.99 6.44

[ Team LiB ]