RE: Sql aggregate woes
SELECT DISTINCT inventory.ItemNumber, SUM(inventory.qty) as invqty where invqty 0 GROUP BY inventory.ItemNumber. All items not aggregated in a select must be also placed in a Group By Clause. Sandra Clark == http://www.shayna.com Training in Cascading Style Sheets and Accessibility -Original Message- From: Terry Troxel [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 9:30 AM To: CF-Talk Subject: Sql aggregate woes 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. I have fixed this in the past, but am drawing a blank. Terry ~| 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:262656 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sql aggregate woes
select distinct i.itemnumber sum(i.qty) as invqty from [your inventory table name here] i group by i.itemnumber having sum(i.qty) 0 On 12/3/06, Terry Troxel [EMAIL PROTECTED] 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. I have fixed this in the past, but am drawing a blank. Terry ~| 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:262657 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sql aggregate woes
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
Re: Sql aggregate woes
Jim Wright wrote: SELECT inventory.ItemNumber, SUM(inventory.qty) as invqty FROM inventory GROUP BY inventory.ItemNumber HAVING SUM(inventory.qty) 0 oops...told you to take the DISTINCT out, and then left it in...see above. ~| 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:262659 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Sql aggregate woes
Thanks all of you. Wow, I really enjoy this list when you all pitch in and help each other like you just did. Really Appreciate it. Terry -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:20 AM To: CF-Talk Subject: Re: Sql aggregate woes Jim Wright wrote: SELECT inventory.ItemNumber, SUM(inventory.qty) as invqty FROM inventory GROUP BY inventory.ItemNumber HAVING SUM(inventory.qty) 0 oops...told you to take the DISTINCT out, and then left it in...see above. ~| 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:262661 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Sql aggregate woes
:) know the feeling, jim...i completely overlooked the distinct, left out a comma between the fields, and left out the table name even though it was right there...lol. all of this snow up here must be affecting my brain cells...all 2 of them. oh, well, at least terry got some valuable feedback on the use of group by and having. On 12/3/06, Jim Wright [EMAIL PROTECTED] wrote: Jim Wright wrote: SELECT inventory.ItemNumber, SUM(inventory.qty) as invqty FROM inventory GROUP BY inventory.ItemNumber HAVING SUM(inventory.qty) 0 oops...told you to take the DISTINCT out, and then left it in...see above. ~| 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:262662 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
