RE: GROUP BY problem...
Which database server are you using? Some allow you the ability to return an aggregate of values in the form of a comma delimited list. You might be able to get your result set to look like this: EMPID NAME OFFICES 1 DaveBoston (NE), Dallas (SW), Miami (SE) 2 JohnSan Diego (W), New Orleans (S) Is that the kind of result set you are looking for? Dave -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Friday, October 02, 2009 11:10 AM To: cf-talk Subject: GROUP BY problem... SELECT employees.empID employees.name, employees_office.theORDER office.office_name areas.area_NAME FROM employees INNER JOIN employees_office ON employees.empID = employees_office.empID INNER JOIN office ON employees_office.id_office = dbo.office.office_id INNER JOIN areas ON areas.empID = employees_office.empID ORDER BY employees.name, employees_office.theORDER OK, here's the problem I need to be able to "GROUP BY" employees.empID so each employee is returned only ONCE (a unique empID), but, I can't include any of the columns in the other tables in the "GROUP BY", because there may be multiple records from those tables. An employee may exist in more than one city and may also be assigned to many areas. So, get the employee only ONCE, but return multiples from the other joined tables. Obviously using just "GROUP BY empID" get us the "columns not included in aggregrate" error. I'm not sure how to fix this. I'd like to do it one query, not multipe queries... Advice? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326847 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: GROUP BY problem...
Relational databases don't work that way. Either you have to return all the columns and have data duplication or you would have to have 2 result sets and match them up with ColdFusion. It is easier to let the database to the extra heavy lifting and return one data set with the duplicated information. You could make multiple columns "address1", "address2", etc but that is considered really bad database design and isn't normalized. Steve -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Friday, October 02, 2009 12:58 PM To: cf-talk Subject: Re: GROUP BY problem... Agha Mehdi wrote: > Why not just > > #name# > > #area_name#, #office_name# > > > I could - but it's way more complicated than that... This single query (example was simplified) is responsible for a number of pages... Case A: multiple results based on search params Case B: a single employee, along with their areas and offices My thinking - if I can get it correct in the query, then that's less data that has to be returned, sorted, whatever and thusly saves a few CPU cycles. Why return five copies of basically the same record if that's not needed, right? But, the cfoutput with a group does work. I just don't think it's the most efficient way of doing it. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326843 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: GROUP BY problem...
Agha Mehdi wrote: > Why not just > > #name# > > #area_name#, #office_name# > > > I could - but it's way more complicated than that... This single query (example was simplified) is responsible for a number of pages... Case A: multiple results based on search params Case B: a single employee, along with their areas and offices My thinking - if I can get it correct in the query, then that's less data that has to be returned, sorted, whatever and thusly saves a few CPU cycles. Why return five copies of basically the same record if that's not needed, right? But, the cfoutput with a group does work. I just don't think it's the most efficient way of doing it. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326842 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: GROUP BY problem...
Why not just #name# #area_name#, #office_name# On Fri, Oct 2, 2009 at 9:09 AM, Les Mizzell wrote: > > SELECT > employees.empID > employees.name, > employees_office.theORDER > office.office_name > areas.area_NAME > FROM employees > INNER JOIN employees_office ON employees.empID = employees_office.empID > INNER JOIN office ON employees_office.id_office = dbo.office.office_id > INNER JOIN areas ON areas.empID = employees_office.empID > ORDER BY employees.name, employees_office.theORDER > > > OK, here's the problem > > I need to be able to "GROUP BY" employees.empID so each employee is > returned only ONCE (a unique empID), but, I can't include any of the > columns in the other tables in the "GROUP BY", because there may be > multiple records from those tables. An employee may exist in more than > one city and may also be assigned to many areas. > > So, get the employee only ONCE, but return multiples from the other > joined tables. > > Obviously using just "GROUP BY empID" get us the "columns not included > in aggregrate" error. I'm not sure how to fix this. I'd like to do it > one query, not multipe queries... > Advice? > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326840 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: GROUP BY problem...
Les, I think the query as is will do what you want. Instead of fixing the query use the Grouping function of cfoutput to only display the employee name once, but the other data as it is. Steve -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Friday, October 02, 2009 12:10 PM To: cf-talk Subject: GROUP BY problem... SELECT employees.empID employees.name, employees_office.theORDER office.office_name areas.area_NAME FROM employees INNER JOIN employees_office ON employees.empID = employees_office.empID INNER JOIN office ON employees_office.id_office = dbo.office.office_id INNER JOIN areas ON areas.empID = employees_office.empID ORDER BY employees.name, employees_office.theORDER OK, here's the problem I need to be able to "GROUP BY" employees.empID so each employee is returned only ONCE (a unique empID), but, I can't include any of the columns in the other tables in the "GROUP BY", because there may be multiple records from those tables. An employee may exist in more than one city and may also be assigned to many areas. So, get the employee only ONCE, but return multiples from the other joined tables. Obviously using just "GROUP BY empID" get us the "columns not included in aggregrate" error. I'm not sure how to fix this. I'd like to do it one query, not multipe queries... Advice? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326839 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Group by problem in select query
> Is there a way around this error message? I am selecting 8 > fields (one is a count) and I do not want a group by clause. > Without a group by clause or if I leave one of the fields out of > the group by clause, I receive this error message: > > Column 'location_city' is invalid in the select list because it > is not contained in an aggregate function and there is no GROUP BY clause. By asking for a COUNT(), then it's an aggrigate query - this means that you have to call either the other columns with aggrigate functions or in the GROUP BY Without calling them with this, it's rather like saying "I want to fly to Europe, but don't want to go in an aeroplane" Philip Arnold ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Group by problem in select query
By their very nature aggregate functions require a group by. If you have no group by, then the entire recordset is the group and location_city would make no sense unless your entire recordset has the same value for location_city - Original Message - From: "Kathy Bergman" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 23, 2000 9:19 AM Subject: Group by problem in select query Is there a way around this error message? I am selecting 8 fields (one is a count) and I do not want a group by clause. Without a group by clause or if I leave one of the fields out of the group by clause, I receive this error message: Column 'location_city' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=sts&body=sts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Group by problem in select query
> I am selecting 8 fields (one is a count) and I do not want a group by clause. Kathy, You have to have a GROUP BY if you are using COUNT(). It's an aggregate function - that means it's meaningless without a GROUP BY. What are you trying to do? Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
