Terry Troxel wrote: > I am having a senior moment and could use some help with a > sql select query I am attempting to use to populate a > listbox with only those items that have inventory. > > SELECT DISTINCT inventory.ItemNumber, SUM(inventory.qty) as > invqty where invqty > 0 > > And I get the error I am not using 'ItenNumber' as part of > an aggregate function.
A couple of things... DISTINCT is unnecessary...by virtue of it using a aggregate function, the result set will be distinct based on the group items. As Sandra pointed out, each element that is in the SELECT statement (or in an ORDER BY), needs to be in a GROUP BY statement. also, invqty > 0 will not work(at least in SQL Server...not sure of your DB here)...to filter on a aggregate function, you need to use the HAVING keyword so your query should look something like... SELECT DISTINCT inventory.ItemNumber, SUM(inventory.qty) as invqty FROM inventory GROUP BY inventory.ItemNumber HAVING SUM(inventory.qty) > 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262658 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
