Explain This

1. Senior Member
Join Date
Dec 1969
Posts
590

## Explain This

OK-<BR><BR>I have a SQL statement<BR><BR>"SELECT SUM(tblOrders_Products.opQty) AS totalOrdered, COUNT(tblOrders_Products.opQty) AS totalOrders, AVG(tblOrders_Products.opQty) AS averageOrdered<BR>FROM tblOrders INNER JOIN tblOrders_Products ON tblOrders.orderID = tblOrders_Products.orderID<BR>WHERE tblOrders.customerID=3)<BR><BR>The results<BR>totalOrdered = 19<BR>totalOrders = 15<BR>averageOrdered = 1<BR><BR>It seems that it is rounding the average? I&#039;m using SQL Server 2k. Any ideas why my answer isn&#039;t 1.26 for averageOrdered?<BR><BR>Thanks!

2. Senior Member
Join Date
Dec 1969
Posts
1,032

## try...

USE AVG(convert(numeric(9,2),AVG(tblOrders_Products.op Qty) ))<BR><BR>I am presuming AVG(tblOrders_Products.opQty) is an integer ???

3. the other steve Guest

## RE: Explain This

my guess is that one of the variables is integer type and I believe if you don&#039;t specify otherwise, the results of operations will be placed in the lowest clarity type of variable among the operands.<BR><BR>So the solution is to convert using cdbl(variable) before or with the operation. Whether the answer is to convert the operands or what you can figure out by experimenting.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•