Explain This

Results 1 to 3 of 3

Thread: Explain This

  1. #1
    Join Date
    Dec 1969

    Default 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. #2
    Join Date
    Dec 1969

    Default 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. #3
    the other steve Guest

    Default 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