> No you can't. When you use aggregate functions in an SQL statement you MUST > put all of the fields you are selecting into the GROUP BY clause. That > means you will need two queries since you want to select more fields than > you want to GROUP BY. > > I wouldn't look down on yourself too much for that. Two queries is not that > big a deal. It's when you put queries inside loops that you deserve to be > shot ;)
Okay, how about this. Is there a way to extract the contents of a structure with a series of functions that can extract distinct members? You could do one of two things as I see it... Perform your join query then... 1. Loop thru the query results populating a structure, then perform some kind of function on the structure to extract the count of unique instances of, say, RentalProperty.ID. I'm looking thru structure functions and checking cflib to see if there are any cool structure manipulation functions that can assist 2. Loop thru the query results populating a list, then performing some kind of function on the list to extract the count of unique instances in th elist. Again, I'd start looking at list functions both built in and a place like cflib too I guess... I'd lean towards the structure only because structures would be more robust... Would that work? I can't think of any reason why there'd be a problem if you were using the *same* query results that you were grouping for output...um, right? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
