aggregate error due to function

[boutwater]

Is there some way to do this:

SELECT (sum(d.cartonsshipped)/m.ctnperskid) AS PalletCount, itemnumber, shipdate
FROM orderdata d
inner join itemmaster m
on d.itemnumber = m.itemnumber
GROUP BY d.itemnumber, d.shipdate
HAVING (sum(d.cartonsshipped)/m.ctnperskid) > 0
ORDER BY d.shipdate, d.itemnumber;

and not get the aggregate error (3122). Im guessing it's giving me the error because i try to do a function in the select area. How can I get around this. Thank you in advance,

Ben

[RayMilhon]

No you're getting the error because your dividing an Aggregate by a field. You will need to change sum(d.cartonsshipped)/m.ctnperskid to
(sum(d.artonsshipped)/sum(m.ctsperskid)) Or some aggregation of m.ctsperskid depending on what your data/needs are.

[boblarson]

And also you would need to make sure your divisor is never null or equal to 0. Otherwise you would need to use some IIF statements to ensure that (because you can't divide by null or by 0).