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