Dividing Results of 2 Select Statements

[RJL1]

I have a query where I'm calculating average pallets per order. this is calculated using two select stements 1) calculates the number of of pallets and 2) calculates the number of orders.

I do not get and error but my result is 0 if I repalce the "/" with a multiply "*" or add "+" I get the right calculation but for some reason division returns 0. The expected result is (11 pallets / 23 orders = avg of .48 pallets per order)

Here is the query I have but I'm not sure if what i'm trying to do is possible the way I have in the script

SELECT

'AVG_PL_ORDER' =

(SELECT
COUNT(SC.INTERNAL_CONTAINER_NUM) FROM SHIPPING_CONTAINER SC
LEFT OUTER JOIN SHIPMENT_HEADER SH WITH(NOLOCK)
  ON SC.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM
WHERE SC.CONTAINER_TYPE = 'PL' AND SC.COMPANY = '317' AND SC.WAREHOUSE = '029'
AND CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
BETWEEN @STARTDATE AND @ENDDATE)

/

(SELECT
COUNT(DISTINCT SH.INTERNAL_SHIPMENT_NUM)
FROM SHIPMENT_HEADER SH WITH(NOLOCK)
WHERE SH.COMPANY = '317' AND SH.WAREHOUSE = '029'
AND CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
BETWEEN @STARTDATE AND @ENDDATE)

Thanks in advance for any help
RJL

 

[lameid]

Without digging into your SQL, on the surface it sounds like a [red]Data Type Precedence[/red] issue (that is the help topic as well).

That is the help topic... you could use cast/convert on your existing columns / fields to solve the issue.

 

[gmmastros]

You're dealing with counts, and counts return integers. When SQL Server does math on integers, it uses integer math. To force it to do fractional match, you need to force it, like this:

SELECT

'AVG_PL_ORDER' =

(SELECT
[!]1.0 * [/!]COUNT(SC.INTERNAL_CONTAINER_NUM) FROM SHIPPING_CONTAINER SC
LEFT OUTER JOIN SHIPMENT_HEADER SH WITH(NOLOCK)
  ON SC.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM
WHERE SC.CONTAINER_TYPE = 'PL' AND SC.COMPANY = '317' AND SC.WAREHOUSE = '029'
AND CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
BETWEEN @STARTDATE AND @ENDDATE)

/

(SELECT
[!]1.0 * [/!]COUNT(DISTINCT SH.INTERNAL_SHIPMENT_NUM)
FROM SHIPMENT_HEADER SH WITH(NOLOCK)
WHERE SH.COMPANY = '317' AND SH.WAREHOUSE = '029'
AND CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
BETWEEN @STARTDATE AND @ENDDATE)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

Both solution worked great. Thanks lameid and gmmastros for the quick help.

On to the next issue

Thanks
RJL