Try this. You just want a total of all records returned for display, right? <cfquery name="myquery"> SELECT p.si_id,s.food_store_id,s.gs_price FROM fsnep_food_store_purchases p, fsnep_food_store s,fsnep_food_store_game_info g WHERE p.food_store_id = s.food_store_id AND g.gi_id = #cookie.fsnep_bargain_hunt_gi_id# </cfquery>
<cfoutput query="myquery"> (single row data here) </cfoutput> Total: <cfoutput>#arraySum(myquery["gs_price"])#</cfoutput> You could also do it with a sub-query. But, this'll be faster and cleaner if you just want the total of all columns for display. On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote: > ok, it sounds like I shouldn't have a GROUP BY at all. I already did the > search that I want and now I just want to total everything. > If I remove everything from the SELECT and remove the GROUP BY then it seems > to total and doesn't give an error, so mini-yay! > > It seems though that I can't also return other columns of information at the > same time? If I do: > SELECT SUM(s.gs_price) AS total_price > I get one return and if I do a query.recordCount, it returns a 1 > but if I do: > SELECT p.si_id,s.food_store_id > it returns 15 records. It's just an altered select. > > I guess I want both. I want the information of 15 records returned with a > total price of the returned records. But this, doesn't work: > SELECT p.si_id,s.food_store_id, SUM(s.gs_price) AS total_price > and actually, I want s.gs_price also returned in the select, not just as a > total_price. > > > thanks for the GROUP BY description Deanna. It seems to be just like the > GROUP in the cfoutput of a query or at least similar. > > >To go back to your original question, what you're asking oracle to do > >in your query is the following: > >Look at all rows of data and find those where the p.si_id and the > >s.food_store_id are equal to other rows. Group all those rows together > >and sum the s.gs_price for those equal combinations. If you could tell > >us in English what you're actually trying to total, we could probably > >help you figure out how to do it. Is it the total of prices by food > >store? If so, drop the p.si_id from the select and group by. Is it the > >total purchases regardless of food store? If so, then drop the > >food_store_id from the select and group by. Is it the total price > >regardless of purchase id or food store id? Then drop both from the > >select and group by. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211621 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
