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



Database Access with Visual Basic. NET
Database Access with Visual Basic .NET (3rd Edition)
ISBN: 0672323435
EAN: 2147483647
Year: 2003
Pages: 97

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net