RE: query sum or group by
I wonder... what database are you using? SQL server has always been pretty helpful with that error -Original Message- From: daniel kessler [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 2:28 PM To: CF-Talk Subject: Re: query sum or group by oh thank you thank you thank you and I really mean it this time - thank you. I am so sick of seeing that error. This puts me back to just trying ot figure the functionality. yes! oh man, thanks. >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: >> ~| 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:211655 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
Re: query sum or group by
oh thank you thank you thank you and I really mean it this time - thank you. I am so sick of seeing that error. This puts me back to just trying ot figure the functionality. yes! oh man, thanks. >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: >> ~| 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:211651 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
Re: query sum or group by
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
Re: query sum or group by
>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. ~| 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:211644 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
Re: query sum or group by
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 On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote: > >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 .505/1/05 > >1 .75 5/7/05 > >21.00 6/1/06 > >2.50 5/1/05 > > > > So, why would I get the error, "not a group by expression" when I add a > column to the group by? I receive this error freqently and never understand > why. In this case, I'm using the food store unique id for each food item. > > Also, since I'm asking, I'm trying to figure out how to get the number of > items in a grouped set or if they should not be grouped but instead sorted, > how do I get a count of those items. In other words, I return a list of > purchased items. I may have 4 drinks and 2 waters. I want to list the > shopping cart just like that: > 4 drink 6oz 3.19 > 2 water 32 oz 1.69 > > but I only know how to return a list. > > Here's a picture of the current setup to hopefully make my explanation > clearer. > http://eatsmart.umd.edu/activities/shopping_cart.gif > under quantity in this case is just the food_item_id. In this case there > are only two unique items and in place of the current food_item_id, I'd like > to put the quantity of each item. > > oh, I'm using the same query as displayed above, though on a different page > this time. > > and sorry for all the DB questions, but I'm just out of my league today. I'm > not really a TOTAL loser. > ~| 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:211643 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
Re: query sum or group by
>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 .505/1/05 >1 .75 5/7/05 >21.00 6/1/06 >2.50 5/1/05 > So, why would I get the error, "not a group by expression" when I add a column to the group by? I receive this error freqently and never understand why. In this case, I'm using the food store unique id for each food item. Also, since I'm asking, I'm trying to figure out how to get the number of items in a grouped set or if they should not be grouped but instead sorted, how do I get a count of those items. In other words, I return a list of purchased items. I may have 4 drinks and 2 waters. I want to list the shopping cart just like that: 4 drink 6oz 3.19 2 water 32 oz 1.69 but I only know how to return a list. Here's a picture of the current setup to hopefully make my explanation clearer. http://eatsmart.umd.edu/activities/shopping_cart.gif under quantity in this case is just the food_item_id. In this case there are only two unique items and in place of the current food_item_id, I'd like to put the quantity of each item. oh, I'm using the same query as displayed above, though on a different page this time. and sorry for all the DB questions, but I'm just out of my league today. I'm not really a TOTAL loser. ~| 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:211635 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
Re: query sum or group by
I gave it a try, with the where clause duplicating the outside one, but it gave me an unusually large number, so I'll have to give it a bit more time. thanks! >Of course, that total_price subselect will undoubtedly need to be >altered with a where clause too, but you know your data, so play with >that some until it's the specific total you want. Then you will have >that column containing the total alongside all your others. > >Ken Ferguson wrote: ~| 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:211629 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
Re: query sum or group by
Of course, that total_price subselect will undoubtedly need to be altered with a where clause too, but you know your data, so play with that some until it's the specific total you want. Then you will have that column containing the total alongside all your others. Ken Ferguson wrote: > How does this work for you? Does this give you what you need? > SELECT g.gi_id, p.si_id,s.food_store_id,s.gs_price, (select > SUM(gs_price) from fsnep_food_store) AS total_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# > > > --Ferg > > ~| 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:211627 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
Re: query sum or group by
How does this work for you? Does this give you what you need? SELECT g.gi_id, p.si_id,s.food_store_id,s.gs_price, (select SUM(gs_price) from fsnep_food_store) AS total_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# --Ferg Eddie Awad wrote: >>SELECT p.si_id,s.food_store_id,s.gs_price, SUM(s.gs_price) AS total_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# >>GROUP BY p.si_id,s.food_store_id,s.gs_price >> >> > >Remove the s.gs_price from the select clause and the group by clause >if you want a sum on this column: > >SELECT p.si_id,s.food_store_id, SUM(s.gs_price) AS total_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# >GROUP BY p.si_id,s.food_store_id > > ~| 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:211626 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
Re: query sum or group by
On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote: > I guess I could also replicate the query with another name and just do a > sum there. Ack. Don't do that. If you want to learn how to do it in a sub-query, then by all means, research subqueries. But doing an extra trip back to the DB is very inefficient and bad design, when all the information you need is already right there in memory for you. ~| 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:211623 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
Re: query sum or group by
>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. I see. I had incorrectly assumed that they could all occupy the SELECT line and that both types of functionality could be done in one query. I guess I could also replicate the query with another name and just do a sum there. Your way worked fine and I appreciate it. I hadn't thought down that line at all and that's the way I'll implement it. thanks! ~| 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:211622 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
Re: query sum or group by
Try this. You just want a total of all records returned for display, right? 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# (single row data here) Total: #arraySum(myquery["gs_price"])# 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
Re: query sum or group by
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. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211616 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
Re: query sum or group by
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 .505/1/05 1 .75 5/7/05 21.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 FROMourtable 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
Re: query sum or group by
Alright, I did that but I have a few questions. First, I still received a total_price of 0.60 with 15 records return of which the first several records had a price of 0.60. Secondly, why should I have removed them from the SELECT and GROUP BY? I'd like to understand better. Also, in this case, what should I do if I also need the gs_price, which in this case, I do. I'm assuming that I can get both the item list and the total in on query. thanks for the help! >Remove the s.gs_price from the select clause and the group by clause >if you want a sum on this column: > >SELECT p.si_id,s.food_store_id, SUM(s.gs_price) AS total_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# >GROUP BY p.si_id,s.food_store_id >-- >Eddie Awad. >http://awads.net/ ~| 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:211612 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
Re: query sum or group by
> SELECT p.si_id,s.food_store_id,s.gs_price, SUM(s.gs_price) AS total_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# > GROUP BY p.si_id,s.food_store_id,s.gs_price Remove the s.gs_price from the select clause and the group by clause if you want a sum on this column: SELECT p.si_id,s.food_store_id, SUM(s.gs_price) AS total_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# GROUP BY p.si_id,s.food_store_id -- Eddie Awad. http://awads.net/ ~| 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:211600 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
query sum or group by
I am trying to SUM a column in oracle. The query works fine until I add the SUM. It seems from the error, that I then have to have a GROUP BY. I guess I don't understand how to use the GROUP BY in this instance or choose which column to GROUP BY. I have googled around. SELECT p.si_id,s.food_store_id,s.gs_price, SUM(s.gs_price) AS total_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# GROUP BY p.si_id,s.food_store_id,s.gs_price If I use all the columns listed in the SELECT, then I don't receive an error but it doesn't seem to be the type of SUM that I'm expecting. For 15 records listing their price I receive: 0.60 0.60 0.60 0.69 0.69 0.89 0.99 1.39 1.39 1.39 1.39 0.79 0.79 0.79 0.79 total price:0.60 ~| 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:211596 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
