Here's the rule. It's pretty simple. You can not select aggregate function (sum, count, max, min, etc) columns and regular columns without using group by. Every column in the select statement must either have an aggregate function, or must be in the group by clause. What you're trying to do above will work if you add food_item and pkg_size to the group by, and total the gs_price column.
On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote: > >I'm a bit confused as to what you're trying to count and total. It > >looks to me like you're now doing a count and price by item, whereas > >before you wanted a grand total. IF you want a count and price by > >item, you'd just do: > > > >SELECT itemname, SUM(pricecolumn) as totalpricebyitem, COUNT(itemid) > >AS countbyitem > >FROM yourtables > >group by itemname > > I'm sorry. I didn't mean to confuse. It's a very similar query as the first > one, but it is a different query. > You're right, except I'm not doing a SUM on the price per item. I'm just > trying to find out how many orange juices there are at whatever price they're > at. I tried adjusting yours and mine to this: > SELECT s.food_item,s.pkg_size,s.gs_price, COUNT(p.food_store_id) AS item_total > FROM fsnep_food_store_purchases p, fsnep_food_store s > WHERE p.food_store_id = s.food_store_id > AND p.gi_id = #cookie.fsnep_bargain_hunt_gi_id# > GROUP BY s.food_item > > but I receive the same error that I always receive, "not a GROUP BY > expression" and I never know why it's not a valid groupby expression. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211645 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
