When you ask Oracle to group by a particular column, it looks for like values within that column and groups those rows of data together. Let's look at some sample data.
CustID PricePaid Date 1 .50 5/1/05 1 .75 5/7/05 2 1.00 6/1/06 2 .50 5/1/05 If you do a query that says Select custid, SUM(pricepaid) AS totalpaid FROM ourtable GROUP BY custid You will get the total that each customer paid. In this case, date is irrelevant. However, if you were to add the date column, you're going to get the data just as it's presented above. Because if the column is in the select clause without an aggregate (sum, count, etc), then it must be in the group by clause. Oracle will look at the combo of the custid column and the datepaid column and realize that there aren't any groupings and give you all the rows back. However, if you say SELECT datepaid, SUM(pricepaid) AS totalpaid FROM ourtable GROUP By datepaid You'll get three rows of data returned. (Two purchases were made on 5/1/05 - so those two rows will be summed. The rest of the rows will be returned "raw.") Does that make sense? 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:211614 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
