Re: Group by??
So I have a query that returns the following.. Client_Code MenuName Client_Name 4938 Test Test Company 9328 Test Test Company 10349 Test Test Company 9283 Test Test 2 Company What I'm trying to do is produce a table for MenuNameClient_Name Client_Code Test Test Company 4938,9328,10349 Test Test 2 Company 9283 So far, I have this.. cfoutput query=currentQuery group=Client_Code tr cfloop list=#event.getArg(displayList)# index=currentField td #currentQuery[currentField][currentQuery.currentRow]# /td /cfloop /tr /cfoutput Which is showing each client_code on a seperate line.. Can someone tell me what I'm doing wrong here? Whenever you use the GROUP attribute of CFOUTPUT, you use a nested CFOUTPUT to display the members of each group. So, you'd need something like this: cfoutput query=... group=Client_Name tr td#MenuName#/td td#Client_Name#/td td cfoutput#Client_Code#,/cfoutput !--- this will actually leave an extra comma at the end, there are various ways to avoid that or remove it --- /td /tr /cfoutput Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352845 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Group by??
Hrm.. Still getting the same result. The code that is actually printing it is #currentQuery[currentField][currentQuery.currentRow]# So I probably have to figure out how to remove the current row (since #currentQuery[currentField]# doesn't work). It's probably forcing it to display just on that row. So I have a query that returns the following.. Whenever you use the GROUP attribute of CFOUTPUT, you use a nested CFOUTPUT to display the members of each group. So, you'd need something like this: cfoutput query=... group=Client_Name tr td#MenuName#/td td#Client_Name#/td td cfoutput#Client_Code#,/cfoutput !--- this will actually leave an extra comma at the end, there are various ways to avoid that or remove it --- /td /tr /cfoutput Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352846 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Group by??
group=Client_Code It should work as long as its contained without a nested cfoutput as Dave mentioned. Also, notice he is grouping by Client_Name instead of the code. Be sure the query results are sorted by that column too. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352848 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Group by??
*facepalms* That did it. I wasn't sorting the query by Client_Name. I was doing it by Client_Code. Thanks guys. I appricate the help. :) group=Client_Code It should work as long as its contained without a nested cfoutput as Dave mentioned. Also, notice he is grouping by Client_Name instead of the code. Be sure the query results are sorted by that column too. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352849 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
re: Group BY
If this is SQL Server, than the GROUP BY has to match the un-aggregated SELECT columns, so it would need to be something like: SELECT COUNT(intMemberID) AS NewMembers, DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) AS Month FROM TABLE GROUP BY DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) Not sure if that's what you're asking. From: Monique Boea moniqueb...@gmail.com Sent: Wednesday, November 03, 2010 2:24 PM To: cf-talk cf-talk@houseoffusion.com Subject: Group BY How can I get this query to group by month? select count(intMemberID)as NewMembers, DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) AS Month FROM TABLE GROUP BY Month(dteAdded) When I ask on google everyone says to add: *GROUP BY Year(SomeDate), Month(SomeDate) * but that doesn't work. thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338804 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Group BY
Your group by has to match your select in most cases... Also, you are using reserved words in your query so that can mess stuff up. Try this instead... Select count(intMemberID) as [NewMembers], dateName(mm, [dteAdded]) + '-' + dateName(, [dteAdded]) as MTH From table Group by dateName(mm, [dteAdded]) + '-' + dateName(, [dteAdded]) Steve -Original Message- From: Monique Boea [mailto:moniqueb...@gmail.com] Sent: Wednesday, November 03, 2010 2:23 PM To: cf-talk Subject: Group BY How can I get this query to group by month? select count(intMemberID)as NewMembers, DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) AS Month FROM TABLE GROUP BY Month(dteAdded) When I ask on google everyone says to add: *GROUP BY Year(SomeDate), Month(SomeDate) * but that doesn't work. thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338805 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Group BY
That was it. I found the solution SELECT DATENAME(mm, article.Created) AS Month, DATENAME(, article.Created) AS Year, COUNT(*) AS Total FROM Articles AS article GROUP BY DATENAME(mm, article.Created), DATENAME(, article.Created) ORDER BY Month, Year DESC At the following link: http://stackoverflow.com/questions/45535/get-month-and-year-from-a-datetime-in-sql-server-2005 Thanks! On Wed, Nov 3, 2010 at 2:28 PM, Jason Fisher ja...@wanax.com wrote: If this is SQL Server, than the GROUP BY has to match the un-aggregated SELECT columns, so it would need to be something like: SELECT COUNT(intMemberID) AS NewMembers, DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) AS Month FROM TABLE GROUP BY DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) Not sure if that's what you're asking. From: Monique Boea moniqueb...@gmail.com Sent: Wednesday, November 03, 2010 2:24 PM To: cf-talk cf-talk@houseoffusion.com Subject: Group BY How can I get this query to group by month? select count(intMemberID)as NewMembers, DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) AS Month FROM TABLE GROUP BY Month(dteAdded) When I ask on google everyone says to add: *GROUP BY Year(SomeDate), Month(SomeDate) * but that doesn't work. thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338806 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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...
Why not just cfoutput query= group=name b#name#/bbr cfoutput #area_name#, #office_name#br /cfoutput /cfoutput On Fri, Oct 2, 2009 at 9:09 AM, Les Mizzell lesm...@bellsouth.net 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...
Agha Mehdi wrote: Why not just cfoutput query= group=name b#name#/bbr cfoutput #area_name#, #office_name#br /cfoutput /cfoutput 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...
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 cfoutput query= group=name b#name#/bbr cfoutput #area_name#, #office_name#br /cfoutput /cfoutput 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...
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 RIGHT()?
Not sure if you can do that, but you might want to try getting the file extension as a calculated column in the query SELECT ( RIGHT( filename, 3 ) ) AS ext Then, in the CFOutput, you could group=ext .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Monday, July 23, 2007 11:11 AM To: CF-Talk Subject: GROUP BY RIGHT()? Hello Guys, I've got a query which returns a load of file names which I'm looking to group by their extension. Is there any way to do this dynamically from the name or do I have to make a separate column in the database? Can I do something like this: cfoutput query=myquery group=right(filename, 3) #myquery.filename# /cfoutput Thanks guys for any help, first time I've done any cf in a while, things are feeling a little rusty. Rob ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284358 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 RIGHT()?
cfquery name=foo datasource=bar SELECT filename, right(filename, 3) AS ext, othercol FROM table ORDER BY right(filename, 3) /cfquery cfoutput query=foo group=ext that, of course, assumes you only have file extensions of 3 characters. .jpeg or .tiff would throw off the logic. I suppose you could run an instr() function inside of a CASE to determine the position of the dot if need be. On 7/23/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Hello Guys, I've got a query which returns a load of file names which I'm looking to group by their extension. Is there any way to do this dynamically from the name or do I have to make a separate column in the database? Can I do something like this: cfoutput query=myquery group=right(filename, 3) #myquery.filename# /cfoutput Thanks guys for any help, first time I've done any cf in a while, things are feeling a little rusty. Rob ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284359 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 RIGHT()?
No, but you could select a column in your query that reads just the first 3 characters of the filename, give it a column alias, order by that column alias, and then use that in your cfoutput group attribute. Something like this (check your particular RDBMS for the Right() function or similar): select filename, right(filename, 3) as lastThreeFileName from my table order by lastThreeFileName cfoutput group=lastThreeFileName/cfoutput On 7/23/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Hello Guys, I've got a query which returns a load of file names which I'm looking to group by their extension. Is there any way to do this dynamically from the name or do I have to make a separate column in the database? Can I do something like this: cfoutput query=myquery group=right(filename, 3) #myquery.filename# /cfoutput Thanks guys for any help, first time I've done any cf in a while, things are feeling a little rusty. Rob ~| ColdFusion 8 beta - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284360 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: GROUP BY RIGHT()?
Thanks guys for the suggestion, I'll agree that the calculated column is probably my best bet, you're right Charlie about that assuming the file extension is only 3 characters, however two file extensions with the same last 3 characters isn't all that likely. Now, I'm having a little trouble with this group by: cfoutput query=VARIABLES.Messages group=type #VARIABLES.Messages.type# /cfoutput For some reason it doesn't appear to group properly, either that or I'm forgetting how to use it properly :-D That code snippet outputs like this: Gif Jpg Gif Txt Gif Vcf See how it's not grouping the gifs properly? Any ideas what I'm missing? Thanks guys, Rob -Original Message- From: Brian Kotek [mailto:[EMAIL PROTECTED] Sent: 23 July 2007 16:24 To: CF-Talk Subject: Re: GROUP BY RIGHT()? No, but you could select a column in your query that reads just the first 3 characters of the filename, give it a column alias, order by that column alias, and then use that in your cfoutput group attribute. Something like this (check your particular RDBMS for the Right() function or similar): select filename, right(filename, 3) as lastThreeFileName from my table order by lastThreeFileName cfoutput group=lastThreeFileName/cfoutput On 7/23/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Hello Guys, I've got a query which returns a load of file names which I'm looking to group by their extension. Is there any way to do this dynamically from the name or do I have to make a separate column in the database? Can I do something like this: cfoutput query=myquery group=right(filename, 3) #myquery.filename# /cfoutput Thanks guys for any help, first time I've done any cf in a while, things are feeling a little rusty. Rob ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284365 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 RIGHT()?
An ORDER BY? Adrian -Original Message- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: 23 July 2007 16:45 To: CF-Talk Subject: RE: GROUP BY RIGHT()? Thanks guys for the suggestion, I'll agree that the calculated column is probably my best bet, you're right Charlie about that assuming the file extension is only 3 characters, however two file extensions with the same last 3 characters isn't all that likely. Now, I'm having a little trouble with this group by: cfoutput query=VARIABLES.Messages group=type #VARIABLES.Messages.type# /cfoutput For some reason it doesn't appear to group properly, either that or I'm forgetting how to use it properly :-D That code snippet outputs like this: Gif Jpg Gif Txt Gif Vcf See how it's not grouping the gifs properly? Any ideas what I'm missing? Thanks guys, Rob ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284369 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 RIGHT()?
did you ORDER BY appropriately? you need to ORDER BY the column(s) on which you're grouping. On 7/23/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Thanks guys for the suggestion, I'll agree that the calculated column is probably my best bet, you're right Charlie about that assuming the file extension is only 3 characters, however two file extensions with the same last 3 characters isn't all that likely. Now, I'm having a little trouble with this group by: cfoutput query=VARIABLES.Messages group=type #VARIABLES.Messages.type# /cfoutput For some reason it doesn't appear to group properly, either that or I'm forgetting how to use it properly :-D That code snippet outputs like this: Gif Jpg Gif Txt Gif Vcf See how it's not grouping the gifs properly? Any ideas what I'm missing? Thanks guys, Rob -Original Message- From: Brian Kotek [mailto:[EMAIL PROTECTED] Sent: 23 July 2007 16:24 To: CF-Talk Subject: Re: GROUP BY RIGHT()? No, but you could select a column in your query that reads just the first 3 characters of the filename, give it a column alias, order by that column alias, and then use that in your cfoutput group attribute. Something like this (check your particular RDBMS for the Right() function or similar): select filename, right(filename, 3) as lastThreeFileName from my table order by lastThreeFileName cfoutput group=lastThreeFileName/cfoutput On 7/23/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Hello Guys, I've got a query which returns a load of file names which I'm looking to group by their extension. Is there any way to do this dynamically from the name or do I have to make a separate column in the database? Can I do something like this: cfoutput query=myquery group=right(filename, 3) #myquery.filename# /cfoutput Thanks guys for any help, first time I've done any cf in a while, things are feeling a little rusty. Rob ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284370 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Group By Hours
Check out datepart. -Original Message- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 01, 2007 7:42 AM To: CF-Talk Subject: Group By Hours Hello Guys, I have a bunch of records of log data, all with a datetime stamp on it, and I'm looking to have SQL Server return them grouped into hours. I've done this before using MySQL I think, using the Hour() function in the query but it would seem that SQL Server doesn't support such a function. The table looks something like this. LogID ClassID DateTime 1 1 01/01/2007 00:01:25 2 1 01/01/2007 00:01:27 3 1 01/01/2007 00:01:28 4 1 01/01/2007 00:01:28 5 2 01/01/2007 00:01:30 6 1 01/01/2007 00:02:01 7 2 01/01/2007 00:02:25 8 1 01/01/2007 00:03:40 9 2 01/01/2007 00:03:55 Any idea on the best way to do this? There could be tones of records per hour you see, and it makes it much simpler to table and chart if they're grouped into hours. I'd also like to perform a count for the number of each type if 'class' there was for each hour, so when outputting the data it looks something like this. Date/Time Class 1 Class 2 01/01/2007 006 3 Make sense? Thanks for any help guys, Rob ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276602 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 Hours
select logid, classid, datetime, datepart(year, datetime) + '/' + datepart(month, datetime) + '/' + datepart(day, datetime) + '/' + datepart(hour, datetime) AS groupbyvalue from table order by datetime That looks sort of weird, but I think what you'll need to do is make sure you're grouping by more than just the hour. Otherwise, in your grouped output, you would have all of that hour for as many different month/day/year combos as are in your database (unless, that is, you're passing in a single date) Pete On 5/1/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Hello Guys, I have a bunch of records of log data, all with a datetime stamp on it, and I'm looking to have SQL Server return them grouped into hours. I've done this before using MySQL I think, using the Hour() function in the query but it would seem that SQL Server doesn't support such a function. The table looks something like this. LogID ClassID DateTime 1 1 01/01/2007 00:01:25 2 1 01/01/2007 00:01:27 3 1 01/01/2007 00:01:28 4 1 01/01/2007 00:01:28 5 2 01/01/2007 00:01:30 6 1 01/01/2007 00:02:01 7 2 01/01/2007 00:02:25 8 1 01/01/2007 00:03:40 9 2 01/01/2007 00:03:55 Any idea on the best way to do this? There could be tones of records per hour you see, and it makes it much simpler to table and chart if they're grouped into hours. I'd also like to perform a count for the number of each type if 'class' there was for each hour, so when outputting the data it looks something like this. Date/Time Class 1 Class 2 01/01/2007 006 3 Make sense? Thanks for any help guys, Rob ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276603 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Group By Hours
Thanks for the suggestion Pete, I'm sure that this datepart() function is defiantly going to be the way to do this, however I'm struggling to get your solution to work. When I try to run that query I get the following error. http://80.244.184.135/teststats.cfm Any ideas? I've not seen that '+ / + datepart' type thing used before, can you explain a little more about what that's actually doing? Is that just building a dynamic date without the minutes and seconds? Thanks, Rob -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: 01 May 2007 13:26 To: CF-Talk Subject: Re: Group By Hours select logid, classid, datetime, datepart(year, datetime) + '/' + datepart(month, datetime) + '/' + datepart(day, datetime) + '/' + datepart(hour, datetime) AS groupbyvalue from table order by datetime That looks sort of weird, but I think what you'll need to do is make sure you're grouping by more than just the hour. Otherwise, in your grouped output, you would have all of that hour for as many different month/day/year combos as are in your database (unless, that is, you're passing in a single date) Pete On 5/1/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Hello Guys, I have a bunch of records of log data, all with a datetime stamp on it, and I'm looking to have SQL Server return them grouped into hours. I've done this before using MySQL I think, using the Hour() function in the query but it would seem that SQL Server doesn't support such a function. The table looks something like this. LogID ClassID DateTime 1 1 01/01/2007 00:01:25 2 1 01/01/2007 00:01:27 3 1 01/01/2007 00:01:28 4 1 01/01/2007 00:01:28 5 2 01/01/2007 00:01:30 6 1 01/01/2007 00:02:01 7 2 01/01/2007 00:02:25 8 1 01/01/2007 00:03:40 9 2 01/01/2007 00:03:55 Any idea on the best way to do this? There could be tones of records per hour you see, and it makes it much simpler to table and chart if they're grouped into hours. I'd also like to perform a count for the number of each type if 'class' there was for each hour, so when outputting the data it looks something like this. Date/Time Class 1 Class 2 01/01/2007 006 3 Make sense? Thanks for any help guys, Rob ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276609 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Group By Hours
Don't Worry, Onto something now with that date part stuff. I'll let you know how I get on. Rob -Original Message- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: 01 May 2007 13:48 To: CF-Talk Subject: RE: Group By Hours Thanks for the suggestion Pete, I'm sure that this datepart() function is defiantly going to be the way to do this, however I'm struggling to get your solution to work. When I try to run that query I get the following error. http://80.244.184.135/teststats.cfm Any ideas? I've not seen that '+ / + datepart' type thing used before, can you explain a little more about what that's actually doing? Is that just building a dynamic date without the minutes and seconds? Thanks, Rob -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: 01 May 2007 13:26 To: CF-Talk Subject: Re: Group By Hours select logid, classid, datetime, datepart(year, datetime) + '/' + datepart(month, datetime) + '/' + datepart(day, datetime) + '/' + datepart(hour, datetime) AS groupbyvalue from table order by datetime That looks sort of weird, but I think what you'll need to do is make sure you're grouping by more than just the hour. Otherwise, in your grouped output, you would have all of that hour for as many different month/day/year combos as are in your database (unless, that is, you're passing in a single date) Pete On 5/1/07, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Hello Guys, I have a bunch of records of log data, all with a datetime stamp on it, and I'm looking to have SQL Server return them grouped into hours. I've done this before using MySQL I think, using the Hour() function in the query but it would seem that SQL Server doesn't support such a function. The table looks something like this. LogID ClassID DateTime 1 1 01/01/2007 00:01:25 2 1 01/01/2007 00:01:27 3 1 01/01/2007 00:01:28 4 1 01/01/2007 00:01:28 5 2 01/01/2007 00:01:30 6 1 01/01/2007 00:02:01 7 2 01/01/2007 00:02:25 8 1 01/01/2007 00:03:40 9 2 01/01/2007 00:03:55 Any idea on the best way to do this? There could be tones of records per hour you see, and it makes it much simpler to table and chart if they're grouped into hours. I'd also like to perform a count for the number of each type if 'class' there was for each hour, so when outputting the data it looks something like this. Date/Time Class 1 Class 2 01/01/2007 006 3 Make sense? Thanks for any help guys, Rob ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276611 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 Hours
Ok, So I now have this grouping as I want it too, using that date part function, I just need a little help with the counting. Look below for my current query and the results returned. !--- Query to Obtain Records --- cfquery name=LOCAL.qGetAllDateRangeForUnitByDay datasource= SELECT DATEPART(month, DateTime) AS myMonth, DATEPART(day, Datetime) AS myDay, DATEPART(year, DateTime) AS myYear, DATEPART(hour, DateTime) AS myHour, COUNT(1) AS SentOk FROMMacLog WHERE DateTime BETWEEN cfqueryparam value=#ARGUMENTS.StartDate# cfsqltype=cf_sql_timestamp / AND cfqueryparam value=#ARGUMENTS.EndDate# cfsqltype=cf_sql_timestamp / AND ThinkTank_ID = cfqueryparam value=#ARGUMENTS.ThinkTankID# cfsqltype=cf_sql_integer maxlength=4 / GROUP BYDATEPART(month, dateTime), DATEPART(day, datetime), DATEPART(year, dateTime), DATEPART(hour, dateTime) /cfquery http://80.244.184.135/teststats.cfm Now that returns all the records and groups them by hour, and also displays a count of the number of records found for each hour. Now I'm looking to count the number of records in each hour that displays a particular value in its LogClass_ID field, something like this. COUNT(WHERE LogClass_ID = 1) AS Sent COUNT(WHERE LogClass_ID = 2) AS Failed COUNT(WHERE LogClass_ID = 3) AS Postponed Or something to that effect. Then for each hour I'll know how many records there are of each log class. Any ideas? Thanks, Rob ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276614 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Group By Hours
It's pretty much as you said, except the syntax is: SUM(CASE WHEN logclass_id = 1 THEN 1 ELSE 0 END) as sent On 5/1/07, Robert Rawlins - Think Blue wrote: Ok, COUNT(WHERE LogClass_ID = 1) AS Sent COUNT(WHERE LogClass_ID = 2) AS Failed COUNT(WHERE LogClass_ID = 3) AS Postponed ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276615 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 Hours
Perfect Deanna, That works nicely, glad I've got you guys on hand, I would have NEVER gotten that one on my own. Thanks a million, Rob -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: 01 May 2007 14:23 To: CF-Talk Subject: Re: Group By Hours It's pretty much as you said, except the syntax is: SUM(CASE WHEN logclass_id = 1 THEN 1 ELSE 0 END) as sent On 5/1/07, Robert Rawlins - Think Blue wrote: Ok, COUNT(WHERE LogClass_ID = 1) AS Sent COUNT(WHERE LogClass_ID = 2) AS Failed COUNT(WHERE LogClass_ID = 3) AS Postponed ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276621 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: GROUP BY error
arrgh, couldn't work it out so have created a list and appended to it on each output so i can use a listfind to prevent duplicate ID's happening. rough but works. thanks for your help. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269574 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: GROUP BY error
When you instruct the system to only return one of a group like that, you have to tell it which of the possible selections to make. So, you need to either group by the other columns (which will return a row for every distinct entry in that column) or use an aggregate function to only return one. You could return the min or max or similar of the other columns, which may solve your problem. --Ben Mike | NZSolutions Ltd wrote: Hi guys, Got a product query (see below) - just want to make sure that not more than one of each product_id is displayed on a page. Tried the GROUP BY function Eg. GROUP BY products.product_id But I get the error... [Macromedia][SQLServer JDBC Driver][SQLServer]Column 'products.product_price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. This error occurs for all columns not included in the group by. How can I achieve this? Thanks mike ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269476 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 error
not sure i follow ben, i don't want to include the category/collection info in the GROUP BY as this will give me the exact same result as not using GROUP BY? could you give an example of using the min/max on the other columns? thanks mate. mike When you instruct the system to only return one of a group like that, you have to tell it which of the possible selections to make. So, you need to either group by the other columns (which will return a row for every distinct entry in that column) or use an aggregate function to only return one. You could return the min or max or similar of the other columns, which may solve your problem. --Ben Mike | NZSolutions Ltd wrote: mike ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269480 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 error
select id, max(otherfield) as otherfield from table, othertable where table.id = othertable.id group by id order by otherfield This is just off the top of my head, but I hope it gets the idea across. --Ben Doom Mike Little wrote: not sure i follow ben, i don't want to include the category/collection info in the GROUP BY as this will give me the exact same result as not using GROUP BY? could you give an example of using the min/max on the other columns? thanks mate. mike When you instruct the system to only return one of a group like that, you have to tell it which of the possible selections to make. So, you need to either group by the other columns (which will return a row for every distinct entry in that column) or use an aggregate function to only return one. You could return the min or max or similar of the other columns, which may solve your problem. --Ben Mike | NZSolutions Ltd wrote: mike ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269482 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-level security theory
Just to throw in my two pennies worth: I have a CMS where there are about 20 websites which use the same editor, file upload etc. I do this the following way - I have a table with the web site specifics in, like the various upload directories, image directories etc, and then a User table. Each user has a list of approved websites, where the Unique ID for the website matches a list of IDs in the User entry. So when they login, I authenticate, then assign the various website variables to a session struct/query which are then used to display the info they require. Using the session scope means you can only assign them the information etc they need. You can then build the various templates you need for web page editing around the variables set in the session scope. T ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260054 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-level security theory
Well, here's what I do. I use Active Directory groups to manage access to different areas of our intranet. There are a few instances where I create pseudo groups from our main business system, but in the near future, that's going to change to use Active Directory as well. I have an OU, in AD, that contains all groups used for security on my web site. If someone needs access to an area, then several people in our dept can add them. Eventually, I will create an interface so that the primary contacts, across campus, can determine, themselves, who has access to their online resources. I use the managedBy attribute to tell who is the group's primary contact. (We have quite a bit of turnover, but accounts don't always get removed from security groups as they should. By putting some of the group administration in the hands of the dept heads, they can help keep our AD up-to-date.) When a user logs in, I query AD using CFLAP. I store their group membership in an array, or query object, in their session scope. Then, where needed, I only have to call a single function that accepts multiple roles. It goes something like this: cfif isUserInGroup(group1,group2,groupn) Most of the intranet is read-only content. However, there are a few areas where some people have more access to the data. In those, cases, I only have to create a new domain group and add them. It has worked great for the last few years and I'm getting ready to do the same thing on the redesign of our intranet. M!ke ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260064 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Group-level security theory
I'm building a content management application targeted at small-businesses. I quite agree with Barney's description. What you describe here would be role based security. However, for small business, a permission based security would probably be more appropriate. Depends how small is you small business. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260070 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Group-level security theory
The typical name for that arrangement is role based security, and it's typical counterpart is permission based security. To align the terminology, let me restate what you said. With role based security, your application has a set of defined roles that users are assigned to. These roles are then checked by the application against the current user's credentials. Typically roles might author, editor, administrator. Role based security is simpler to manage, but less flexible. With permission based security, your applications has a set of defined permissions that can be used to build permission groups. Then users are assigned to permission groups. These permissions are then checked by the application against the current user's credentials (via the groups the user is assigned to). Typical permissions might be view_documents, add_document, edit_document, delete_document, post_document. Permission based security is more complex to manage (because you must also manage the groups themselves), but it is more flexible. What should be obvious is that if you the developer define the permission groups in a permission based security system, you've created a role based system. As such, I'd almost always recommend going with the permission based system from the outset, and optionally hiding group creation (providing a set of default groups) so it behaves exactly like role based security. Then if your user's require it, you just need to expose the group management to them to give them all the flexibility. Regarding implementation, you always have to have something akin to isUserAuthorized(NAME_OF_SOMETHING). In your case it's a role name. In a permission based system it's a permission name. How it's checked is up to you, but I go with an immutable 'permission' table in my DB that stores all the permissions the system will check against, so that I can build groups out of them exactly like I'd build any other sort of many-to-many relationship in the DB. You do have to be careful about ensuring the permission that the application checks against are identical to the ones in the database, but unless your application is very fluid, that's usually not much of a hassle. cheers, barneyb On 11/11/06, Pete Ruckelshaus [EMAIL PROTECTED] wrote: Hi all, I'm building a content management application targeted at small-businesses. Currently, I am using group-level security (individual users are assigned to groups, groups are granted or declined rights at the page level). Unfortunately, this method requires that I hard code the groups that are authorized access to the page. I'm adding quite a bit of functionality and clients have asked for more control over groups (i.e. limiting access to calendar, news, content admin, etc.) and would like to move away from hard-coding this and moving it into a database. Has anyone done this, and what approach did you take? Thanks, Pete ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260043 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Group-level security theory
Barney gave a pretty good overview. When I wrote AccessMonger, I pretty much followed the same model he describes. Permissions give you absolutely granular control over literally anything you please, but as your system grows you could potentially wind up with zillions of permissions. To help manage that I used groups to group together bundles of permissions for typical classes of users. So you would create a typical manager group where that group has add, edit and delete permissions over a variety of areas. You create this and other default, typical groups in an area reserved for same. Then when a user comes along who you want to give manager permissions you go to the user's record and pick that permission group from a list. If you then want to customize that individual user's granted permissions beyond the quasi-generic role you just assigned to them, you will need to be able to grant the user individual permissions on a one-by-one basis. The real secret to a permission-based system is the management tools you build to manage what is going to wind up being a very complex -- but very flexible -- system. Make sure you build global tools (remove Permission X from all user records, and replace permission X with Permission Y... that sort of thing). -- [EMAIL PROTECTED] Janitor, MSB Web Systems mysecretbase.com ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260048 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 error
You could either do something like selecting the content as a substring (which will work in group by) or you could skip the Group BY and run the count as sub-query SELECT ... SUBSTRING( clubroom.cb_content, 0, 1000 ) AS cb_content ... FROM ... GROUP BY ... SUBSTRING( clubroom.cb_content, 0, 1000 ) ... Or maybe... SELECT ... ( SELECT COUNT(*) FROM ) FROM ... WHERE ... ... Ben Nadel www.bennadel.com Certified Advanced ColdFusion Developer Need Help? www.bennadel.com/ask-ben/ -Original Message- From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 6:37 PM To: CF-Talk Subject: GROUP BY error Hi guys, I have the following query... SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified, clubroom.cb_content, clubroom.cb_isArchive, COUNT(clubroom_images.cbi_id) AS image_count FROM clubroom LEFT OUTER JOIN clubroom_images ON clubroom.cb_id = clubroom_images.cb_id GROUP BY clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified, clubroom.cb_content, clubroom.cb_isArchive ORDER BY clubroom.cb_dCreated DESC And get the following error... Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. Now I have checked the elements and it appears that the error is relating to the cb_content field which is a text field. When this is removed from the query the query runs fine. In order to use the COUNT function I need to have the GROUP BY ?? and I also need the cb_content field. Any help would be greatly appreciated by this SQL dummie! Mike :o) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251552 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 error
sort of like this? SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified, clubroom.cb_content, clubroom.cb_isArchive, ( SELECT COUNT(cbi_id) AS image_count FROM clubroom_images ) FROM clubroom LEFT OUTER JOIN clubroom_images ON clubroom.cb_id = clubroom_images.cb_id ORDER BY clubroom.cb_dCreated DESC thanks for the prompt response. Mike :o) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251554 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: GROUP BY error
hmmm... now i cannot get at my getArticles.image_count variable ?? sort of like this? SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified, clubroom.cb_content, clubroom.cb_isArchive, ( SELECT COUNT(cbi_id) AS image_count FROM clubroom_images ) FROM clubroom LEFT OUTER JOIN clubroom_images ON clubroom.cb_id = clubroom_images.cb_id ORDER BY clubroom.cb_dCreated DESC thanks for the prompt response. Mike :o) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251555 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: GROUP BY error
Sorry, I was on the phone for a bit: SELECT r.cb_id, r.cb_dCreated, r.cb_dModified, r.cb_content, r.cb_isArchive, ( SELECT COUNT( * ) FROM clubroom_images i WHERE i.cb_id = r.cb_id ) AS image_count FROM clubroom r ORDER BY r.cb_dCreated DESC ... Ben Nadel www.bennadel.com Certified Advanced ColdFusion Developer Need Help? www.bennadel.com/ask-ben/ -Original Message- From: Mike Little [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 6:39 PM To: CF-Talk Subject: Re: GROUP BY error hmmm... now i cannot get at my getArticles.image_count variable ?? sort of like this? SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified, clubroom.cb_content, clubroom.cb_isArchive, ( SELECT COUNT(cbi_id) AS image_count FROM clubroom_images ) FROM clubroom LEFT OUTER JOIN clubroom_images ON clubroom.cb_id = clubroom_images.cb_id ORDER BY clubroom.cb_dCreated DESC thanks for the prompt response. Mike :o) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251558 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 function for MS SQL Server Express 2005?
I've used it. Can you show us the SQL you are having a problem with? -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243360 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: Group By function for MS SQL Server Express 2005?
Here is a simple example using the Northwind database in SQL Server The key to the GROUP BY clause is that is needs to be used in aggregate functions SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID GROUP BY P.CategoryID, C.CategoryName -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243362 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: Group By function for MS SQL Server Express 2005?
Ok...I had read a message that mentioned the aggregate function requirement...but how would I write my example query to include an (unneeded?) aggregate function? (And why would there be an aggregate requirement anyway?) Query (MySQL version): Select * from books group by genre -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:31 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? Here is a simple example using the Northwind database in SQL Server The key to the GROUP BY clause is that is needs to be used in aggregate functions SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID GROUP BY P.CategoryID, C.CategoryName ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243364 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: Group By function for MS SQL Server Express 2005?
I finally did get Express to give me the results I wanted... Here's the solution the Query Builder gave: Select Count(*) as Expr2, Book_Genre from Books Group By Book_Genre Order By Book_Genre Seems strange to require an unneeded aggregate function... Rick -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:43 PM To: CF-Talk Subject: RE: Group By function for MS SQL Server Express 2005? Ok...I had read a message that mentioned the aggregate function requirement...but how would I write my example query to include an (unneeded?) aggregate function? (And why would there be an aggregate requirement anyway?) Query (MySQL version): Select * from books group by genre -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:31 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? Here is a simple example using the Northwind database in SQL Server The key to the GROUP BY clause is that is needs to be used in aggregate functions SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID GROUP BY P.CategoryID, C.CategoryName ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243365 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: Group By function for MS SQL Server Express 2005?
But it is needed, that query is returning the count of the books per book_genre On 6/13/06, Rick Faircloth [EMAIL PROTECTED] wrote: I finally did get Express to give me the results I wanted... Here's the solution the Query Builder gave: Select Count(*) as Expr2, Book_Genre from Books Group By Book_Genre Order By Book_Genre Seems strange to require an unneeded aggregate function... Rick -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:43 PM To: CF-Talk Subject: RE: Group By function for MS SQL Server Express 2005? Ok...I had read a message that mentioned the aggregate function requirement...but how would I write my example query to include an (unneeded?) aggregate function? (And why would there be an aggregate requirement anyway?) Query (MySQL version): Select * from books group by genre -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:31 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? Here is a simple example using the Northwind database in SQL Server The key to the GROUP BY clause is that is needs to be used in aggregate functions SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID GROUP BY P.CategoryID, C.CategoryName ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243366 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: Group By function for MS SQL Server Express 2005?
Ok, I see what's going on. You want to out put your records in a group, thats a CF issue, not a SQL issue. What you need to do is to use ORDER BY to get your records in the correct order then user the GROUP attribute of the CFOUTPUT tag GROUP BY in SQL is for compiling related data and performing calculations, such as in my example, I wanted to know how many products were in the Products table, but instead of just knowing how many altogether, I wanted to know BY category, which is what the SQL GROUP BY does. -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243368 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: Group By function for MS SQL Server Express 2005?
Are you trying to get a count of all of the books by Genre? Or are you trying to get a list of all of the genres? -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243369 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: Group By function for MS SQL Server Express 2005?
But why does it require that the count be calculated if I don't need that information? -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 1:05 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? But it is needed, that query is returning the count of the books per book_genre On 6/13/06, Rick Faircloth [EMAIL PROTECTED] wrote: I finally did get Express to give me the results I wanted... Here's the solution the Query Builder gave: Select Count(*) as Expr2, Book_Genre from Books Group By Book_Genre Order By Book_Genre Seems strange to require an unneeded aggregate function... Rick -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:43 PM To: CF-Talk Subject: RE: Group By function for MS SQL Server Express 2005? Ok...I had read a message that mentioned the aggregate function requirement...but how would I write my example query to include an (unneeded?) aggregate function? (And why would there be an aggregate requirement anyway?) Query (MySQL version): Select * from books group by genre -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:31 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? Here is a simple example using the Northwind database in SQL Server The key to the GROUP BY clause is that is needs to be used in aggregate functions SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID GROUP BY P.CategoryID, C.CategoryName ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243371 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: Group By function for MS SQL Server Express 2005?
What information are you trying to get then? On 6/13/06, Rick Faircloth [EMAIL PROTECTED] wrote: But why does it require that the count be calculated if I don't need that information? -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 1:05 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? But it is needed, that query is returning the count of the books per book_genre On 6/13/06, Rick Faircloth [EMAIL PROTECTED] wrote: I finally did get Express to give me the results I wanted... Here's the solution the Query Builder gave: Select Count(*) as Expr2, Book_Genre from Books Group By Book_Genre Order By Book_Genre Seems strange to require an unneeded aggregate function... Rick -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:43 PM To: CF-Talk Subject: RE: Group By function for MS SQL Server Express 2005? Ok...I had read a message that mentioned the aggregate function requirement...but how would I write my example query to include an (unneeded?) aggregate function? (And why would there be an aggregate requirement anyway?) Query (MySQL version): Select * from books group by genre -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 12:31 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? Here is a simple example using the Northwind database in SQL Server The key to the GROUP BY clause is that is needs to be used in aggregate functions SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID GROUP BY P.CategoryID, C.CategoryName ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243373 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: Group By function for MS SQL Server Express 2005?
Ok...I had read a message that mentioned the aggregate function requirement...but how would I write my example query to include an (unneeded?) aggregate function? (And why would there be an aggregate requirement anyway?) Query (MySQL version): Select * from books group by genre The GROUP BY clause requires an aggregate because its sole purpose is to let you perform aggregations on subsets of data returned by your query, instead of on the entire set. I don't use MySQL very much, but I can't even imagine what your example query would return, since there is no aggregation upon which to group. How, in your example, does the query differ from one with no GROUP BY clause? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243379 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: Group By function for MS SQL Server Express 2005?
Just a list of the genres to fill out a dropdown list... SELECT DISTINCT Genre FROM Book Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243400 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: Group By function for MS SQL Server Express 2005?
That's right...exactly the conclusion I thought of after I emailed. I should have just used the DISTINCT function. Question: What's the benefit to using the alias? Is there a benefit or is its usage just required? Could it just be: Select distinct Book_Genre from Books order by Book_Genre ??? Rick -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 3:55 PM To: CF-Talk Subject: Re: Group By function for MS SQL Server Express 2005? SELECT DISTINCT(Book_Genre) AS MyGenre FROM Books ORDER BY Book_Genre This will accomplish what you want. =] ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243404 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: Group By function for MS SQL Server Express 2005?
Yep...it was one of those duh moments... Thanks for your help. Rick -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 4:41 PM To: CF-Talk Subject: RE: Group By function for MS SQL Server Express 2005? Just a list of the genres to fill out a dropdown list... SELECT DISTINCT Genre FROM Book Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243405 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: Group By function for MS SQL Server Express 2005?
Question: What's the benefit to using the alias? Is there a benefit or is its usage just required? Could it just be: Select distinct Book_Genre from Books order by Book_Genre You don't need an alias. SELECT DISTINCT fieldname FROM tablename will do. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243406 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: Group By function for MS SQL Server Express 2005?
Not really, it's just a habit. I like to explicitlly control the name of the resulting variable. Dave is right, as usual, you can just leave it and it will retain it's given column name. -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243408 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: Group - but with no order specified
Also the GROUP BY clasue in SQL is a different beast than the group attribute in cfquery It is, and I think it's more what you're looking for. You don't have to order a group by. By default, depending on your query, it would order it the way it was entered. SQL is really quite awesome, and a super huge thing for CF devs to know about. I can't state enough how much simpler life is when you know about groups and joins and whatnot. Groups and joins are some of the most complex queries you will build, but the performance gain is such that it's WELL worth digging in and learning what's possible. The idea is that the DB can do a lot of stuff WAY faster than CF can. That's what it's designed for. Not utilizing it, and re-inventing the wheel within your CF code is asking for lengthy CF code and slow execution. SQL's also a sticky part, and a good place for abstraction. I can appreciate Isaac's SQL stuff. There are a TON of nifty features in MySQL that plain aren't available in MSSQL, and vice versa, so unless you are organized, switching between the two can be a bitch. 'specially if you don't have your queries all in one spot, or at least sorta separate. The performance gains when using said proprietary functions can be significant. Not to mention ease of use. The MySQL group_concat() is freaking awesome, as is find_in_set(). They can make life easy, until you have to switch to some other DB (oracle has some great functions as well). I guess it boils down to the thing about linux and filesystems, and what you're after. It's the whole optimization morass, where optimization refers to speed, but depending on the frame of reference, you get totally different output. One super huge gain in one area doesn't do ANY good if it drags down another area more than the total time saved (or even the same amount, really). If you're looking at a 20 year frame, what's important is pretty different than if you're looking at 2 years. Keeping both in your head at once is a trip, and brings to mind the whole flying a helicopter analogy. (I must add that I love my Blade CP, an RC heli). Eh. Guess I could have kept this short and said look into SQL... Bah. I think the real problem is one of quantum physics, there is no objective reality. Impossible to achieve, by some crazy conundrum of existence. So we make do. We pretend that there is such a thing as objectivity, for the most part. But it's a sham! ;-) I did enjoy the leaky abstraction article, it did say (much betterly, I might add ;) much of what I was getting at. Good stuff to think about. It didn't come off as saying OO wasn't worthy tho... just that the truth lies somewhere between you and me. The logic seemed sound. Pragmatic, even. I guess articles like those are kind of Gödel-ish: We had it figured out man! Why'd you go and do that! =] ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237645 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: Group - but with no order specified
Only order the groups, not the subgroups? On 4/11/06, Les Mizzell [EMAIL PROTECTED] wrote: Is there a way to group output in a query and keep the groups together, but with no order specified anywhere? cfquery Select myGROUP, mySubGROUP from myTABLE !---(NO ORDER BY)--- /cfquery I need output like: 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 If you just do the below, it doesn't keep the groups together since they're not together in the database... cfoutput group=myGROUP 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 /cfoutput Outputs (depending on what's in the database) 1. myGROUP a. mySUBGOUP 1 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 1. myGROUP b. mySUBGOUP 2 If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I say you can't have it both ways. You have to have a sort order to keep the groups together. Or is there a weird work around for this? Yea - stupid I know, but clients want what they want... ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237455 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: Group - but with no order specified
This idea may seem complicated, but it should work. Put the ORDER BY in the SQL. Create an empty array. Do your cfoutput with the 'group' attribute. Use cfsavecontent to put each Group's output into a variable in the array. Then you can use some kind of randomizer to grab the output from the array. This would have to be a randomizer that doesn't allow repeating (forget the technical term for that). Good luck. On 4/11/06, Les Mizzell [EMAIL PROTECTED] wrote: Is there a way to group output in a query and keep the groups together, but with no order specified anywhere? cfquery Select myGROUP, mySubGROUP from myTABLE !---(NO ORDER BY)--- /cfquery I need output like: 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 If you just do the below, it doesn't keep the groups together since they're not together in the database... cfoutput group=myGROUP 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 /cfoutput Outputs (depending on what's in the database) 1. myGROUP a. mySUBGOUP 1 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 1. myGROUP b. mySUBGOUP 2 If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I say you can't have it both ways. You have to have a sort order to keep the groups together. Or is there a weird work around for this? Yea - stupid I know, but clients want what they want... ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237456 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: Group - but with no order specified
I don't know what DB you're using, but have you tried using 'group by'? Something like this: select fname, lname, address, city, state, zip from contacts group by state, city, zip -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 1:00 PM To: CF-Talk Subject: Group - but with no order specified Is there a way to group output in a query and keep the groups together, but with no order specified anywhere? cfquery Select myGROUP, mySubGROUP from myTABLE !---(NO ORDER BY)--- /cfquery I need output like: 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 If you just do the below, it doesn't keep the groups together since they're not together in the database... cfoutput group=myGROUP 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 /cfoutput Outputs (depending on what's in the database) 1. myGROUP a. mySUBGOUP 1 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 1. myGROUP b. mySUBGOUP 2 If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I say you can't have it both ways. You have to have a sort order to keep the groups together. Or is there a weird work around for this? Yea - stupid I know, but clients want what they want... ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237457 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: Group - but with no order specified
CF doesn't care if the records are actually in order, it just processes the outer loop when there's a change of value. If myGROUP has a numeric primary key you could do your ORDER BY on that, rather than on the name of it. Failing that, you could sort in some strange way that might look random, but isn't. Like on the 2nd through 5th character in the name. Failing THAT, I'd suggest creating a SORT_ORDER (INTEGER) column for each of the myGROUP records and ask your client what order he'd like them in. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 3:00 PM To: CF-Talk Subject: Group - but with no order specified Is there a way to group output in a query and keep the groups together, but with no order specified anywhere? cfquery Select myGROUP, mySubGROUP from myTABLE !---(NO ORDER BY)--- /cfquery I need output like: 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 If you just do the below, it doesn't keep the groups together since they're not together in the database... cfoutput group=myGROUP 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 /cfoutput Outputs (depending on what's in the database) 1. myGROUP a. mySUBGOUP 1 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 1. myGROUP b. mySUBGOUP 2 If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I say you can't have it both ways. You have to have a sort order to keep the groups together. Or is there a weird work around for this? Yea - stupid I know, but clients want what they want... ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237458 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: Group - but with no order specified
I'd suggest creating a SORT_ORDER (INTEGER) column for each of the myGROUP records and ask your client what order he'd like them in. This is probably what's going to have to happen. Otherwise, Yoda says, Solutions there are, but convoluted are they!. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237460 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: Group - but with no order specified
If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I'm not trying to sound like a wiseass, but just point out a perspective: if the client doesn't care what order they're in, what's the harm in ordering them? If that doesn't satisfy them, maybe try an ORDER BY on the primary key of whatever's giving you myGroup. -Joe -- Get Glued! The Model-Glue ColdFusion Framework http://www.model-glue.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237461 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: Group - but with no order specified
It may well be that they don't not care. They may be specifically asking that they not be in any sort of discernible order. Why are you using the group in the cfoutput instead of in the query? --Ferg Joe Rinehart wrote: If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I'm not trying to sound like a wiseass, but just point out a perspective: if the client doesn't care what order they're in, what's the harm in ordering them? If that doesn't satisfy them, maybe try an ORDER BY on the primary key of whatever's giving you myGroup. -Joe -- Get Glued! The Model-Glue ColdFusion Framework http://www.model-glue.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237462 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: Group - but with no order specified
If you've got access to mySQL then just ORDER BY RAND(). !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Ken Ferguson [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 3:01 PM To: CF-Talk Subject: Re: Group - but with no order specified It may well be that they don't not care. They may be specifically asking that they not be in any sort of discernible order. Why are you using the group in the cfoutput instead of in the query? --Ferg Joe Rinehart wrote: If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I'm not trying to sound like a wiseass, but just point out a perspective: if the client doesn't care what order they're in, what's the harm in ordering them? If that doesn't satisfy them, maybe try an ORDER BY on the primary key of whatever's giving you myGroup. -Joe -- Get Glued! The Model-Glue ColdFusion Framework http://www.model-glue.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237463 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: Group - but with no order specified
I'm not trying to sound like a wiseass, but just point out a perspective: if the client doesn't care what order they're in, what's the harm in ordering them? Client wants it in the order he typed it in, not an alpha sort or anything. What the client don't get though is that he's not necessarily typing stuff in grouped together either. So, I'll give them a sort by box and make it their problem to define the sort. If sort by ain't defined, they'll get alpha, like it or not. It's been alpha for the last year anyway Just for reference, it's the list of contacts in the right panel here: http://www.nmrs.com/news/nelson-mullins-newsletter_detail.cfm?id=2406090D-BCD2-35D3-D151001A53DF4C73 What's wrong with an alpha sort? I've *no* idea! Either way, I'm happy to take more of their money!!! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237464 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: Group - but with no order specified
If he's typing it in in order then why not just order it by the autointeger field? !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 2:53 PM To: CF-Talk Subject: Re: Group - but with no order specified I'm not trying to sound like a wiseass, but just point out a perspective: if the client doesn't care what order they're in, what's the harm in ordering them? Client wants it in the order he typed it in, not an alpha sort or anything. What the client don't get though is that he's not necessarily typing stuff in grouped together either. So, I'll give them a sort by box and make it their problem to define the sort. If sort by ain't defined, they'll get alpha, like it or not. It's been alpha for the last year anyway Just for reference, it's the list of contacts in the right panel here: http://www.nmrs.com/news/nelson-mullins-newsletter_detail.cfm?id=2406090D-BC D2-35D3-D151001A53DF4C73 What's wrong with an alpha sort? I've *no* idea! Either way, I'm happy to take more of their money!!! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237466 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: Group - but with no order specified
Correct, but if a filed being 'GROUPED' in CF is not ORDERed in SQL, then
disinct items in the 'outer loop' may beoutput more than once.
For example:
cfset test = QueryNew('name,item') /
cfset queryAddRow(test,6) /
cfset querySetCell(test,'name','Bob',1) /
cfset querySetCell(test,'item','book',1) /
cfset querySetCell(test,'name','Bob',2) /
cfset querySetCell(test,'item','ball',2) /
cfset querySetCell(test,'name','Jane',3) /
cfset querySetCell(test,'item','book',3) /
cfset querySetCell(test,'name','Mary',4) /
cfset querySetCell(test,'item','dress',4) /
cfset querySetCell(test,'name','Jane',5) /
cfset querySetCell(test,'item','shoe',5) /
cfset querySetCell(test,'name','Bob',6) /
cfset querySetCell(test,'item','bat',6) /
cfoutput query=test group=name
#name#br/
cfoutput--#item#br//cfoutput
/cfoutput
Will give you:
Bob
--book
--ball
Jane
--book
Mary
--dress
Jane
--shoe
Bob
--bat
Also the GROUP BY clasue in SQL is a different beast than the group
attribute in cfquery
On 4/11/06, Everett, Al (NIH/NIGMS) [C] [EMAIL PROTECTED] wrote:
CF doesn't care if the records are actually in order, it just processes
the outer loop when there's a change of value.
--
Scott Stroz
Boyzoid.com
___
Some days you are the dog,
Some days you are the tree.
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237467
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: Group Query help.
I have never tried to add MAXROWS to an CFOUTPUT Statement of a Group before, so I am not use it Can be done, however the only other way I can Think of is by creating your own counter and increment It by 1 each time until you get the desired number Of records. CFSET grpCounter = 0 CFOUTPUT GROUP=grpQuery CFSET grpCounter = grpCounter + 1 CFIF grpCounter LTE desiredCount -Original Message- From: Nomad [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 11:49 PM To: CF-Talk Subject: Group Query help. Hello! How do I limit the number of rows returned per group in a grouped query. My query: Select pid, Details, Brand from Products group by brand order by price This query returns: pid Details Brand 1abcdMacromedia 2efgh Macromedia 3efgh Corel 4abcf Corel 5errerAdobe etc etc. What I want to achieve is to limit the max number of rows returned per group to say 5. So if there are 10 Macromedia products in my db only 5 rows would show up. Please note that my aim is not to limit the total number of rows returned with a Top statement or similar. Thanks in advance. Ben 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:221634 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=11639.10644.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54 ~| 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:227078 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: Group Query help.
I'm no SQL expert but I don't know if what you're describing is possible. Retrieve a max of 5 rows for each group in the database. I think that's going to have to be done in CF. !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Nomad [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 10:49 PM To: CF-Talk Subject: Group Query help. Hello! How do I limit the number of rows returned per group in a grouped query. My query: Select pid, Details, Brand from Products group by brand order by price This query returns: pid Details Brand 1abcdMacromedia 2efgh Macromedia 3efgh Corel 4abcf Corel 5errerAdobe etc etc. What I want to achieve is to limit the max number of rows returned per group to say 5. So if there are 10 Macromedia products in my db only 5 rows would show up. Please note that my aim is not to limit the total number of rows returned with a Top statement or similar. Thanks in advance. Ben 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:221634 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=11639.10644.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54 ~| 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:227086 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: Group Query help.
The only thing I can think of off the top of my head that MIGHT be able to help is 'LIMIT' in MySQL... even then I THINK you can only use that at the end of a query. But with nested selects, you might come up with something. What type of database are you using? That might trigger the brain of some SQL gurus around here... but I'm afraid it's something that you're probably not going to get done in a single query. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 9:21 AM To: CF-Talk Subject: RE: Group Query help. I'm no SQL expert but I don't know if what you're describing is possible. Retrieve a max of 5 rows for each group in the database. I think that's going to have to be done in CF. !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Nomad [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 10:49 PM To: CF-Talk Subject: Group Query help. Hello! How do I limit the number of rows returned per group in a grouped query. My query: Select pid, Details, Brand from Products group by brand order by price This query returns: pid Details Brand 1abcdMacromedia 2efgh Macromedia 3efgh Corel 4abcf Corel 5errerAdobe etc etc. What I want to achieve is to limit the max number of rows returned per group to say 5. So if there are 10 Macromedia products in my db only 5 rows would show up. Please note that my aim is not to limit the total number of rows returned with a Top statement or similar. Thanks in advance. Ben 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:221634 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=11639.10644.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54 ~| 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:227089 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: Group Query help.
Hi Bobby, I am using SQl Server 2000. Ben Sorry for the delay in my reply. - Original Message - From: Bobby Hartsfield [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Friday, December 16, 2005 3:34 AM Subject: RE: Group Query help. The only thing I can think of off the top of my head that MIGHT be able to help is 'LIMIT' in MySQL... even then I THINK you can only use that at the end of a query. But with nested selects, you might come up with something. What type of database are you using? That might trigger the brain of some SQL gurus around here... but I'm afraid it's something that you're probably not going to get done in a single query. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 9:21 AM To: CF-Talk Subject: RE: Group Query help. I'm no SQL expert but I don't know if what you're describing is possible. Retrieve a max of 5 rows for each group in the database. I think that's going to have to be done in CF. !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Nomad [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 10:49 PM To: CF-Talk Subject: Group Query help. Hello! How do I limit the number of rows returned per group in a grouped query. My query: Select pid, Details, Brand from Products group by brand order by price This query returns: pid Details Brand 1abcdMacromedia 2efgh Macromedia 3efgh Corel 4abcf Corel 5errerAdobe etc etc. What I want to achieve is to limit the max number of rows returned per group to say 5. So if there are 10 Macromedia products in my db only 5 rows would show up. Please note that my aim is not to limit the total number of rows returned with a Top statement or similar. Thanks in advance. Ben 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:221634 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=11639.10644.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54 ~| 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:227112 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: group by date, missing something
Jochem, Thx. I've reread your posts a few times. It will help next time. -- gil -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Thursday, June 23, 2005 6:41 PM To: CF-Talk Subject: Re: group by date, missing something mayo wrote: SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal FROM sales GROUP by theMonth I know you already know how to solve your problem, but please bear with me :) The reason that your query isn't supposed to work (it will work in some implementations) is that DBMS's are supposed to derive the result from queries in a specific way. First, they should take the FROM clause, expand the column list from all the tables listed and remove any columns wrong JOIN with the NATURAL or USING modifiers. Then they should prune all the rows that do not match the WHERE clause. After that they should process the GROUP BY, then the HAVING and finally the SELECT. So there you have it: at the point where the DBMS has to do the actual grouping, the SELECT clause isn't processed yet so the alias that is assigned in the SELECT is not known yet. And that is actually a good thing because it removes any ambiguousness from the case where table X has the fields Y and Z and you issue the query SELECT y AS z FROM x WHERE z = 1. Since the alias z is not known yet the z in the WHERE clause has to refer to the column z. This order of processing is one of the reasons why I always start writing my queries by writing the FROM, then I write the WHERE, and then the whole rest. Jochem ~| 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:210492 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: group by date, missing something
mayo wrote: SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal FROM sales GROUP by theMonth I know you already know how to solve your problem, but please bear with me :) The reason that your query isn't supposed to work (it will work in some implementations) is that DBMS's are supposed to derive the result from queries in a specific way. First, they should take the FROM clause, expand the column list from all the tables listed and remove any columns wrong JOIN with the NATURAL or USING modifiers. Then they should prune all the rows that do not match the WHERE clause. After that they should process the GROUP BY, then the HAVING and finally the SELECT. So there you have it: at the point where the DBMS has to do the actual grouping, the SELECT clause isn't processed yet so the alias that is assigned in the SELECT is not known yet. And that is actually a good thing because it removes any ambiguousness from the case where table X has the fields Y and Z and you issue the query SELECT y AS z FROM x WHERE z = 1. Since the alias z is not known yet the z in the WHERE clause has to refer to the column z. This order of processing is one of the reasons why I always start writing my queries by writing the FROM, then I write the WHERE, and then the whole rest. Jochem ~| 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:210451 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: group by date, missing something
Did you try putting MONTH(saleDate) directly in the GROUP BY clause? cheers, barneyb On 6/22/05, mayo [EMAIL PROTECTED] wrote: I'm trying to do a group by date on an access db. I have a list of sales and want to group by month and year. I can't get even a test run to work SQL below: SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal FROM sales GROUP by theMonth Thx -- gil -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| 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:210301 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: group by date, missing something
SELECT sum(saleTotal), saledate FROM sales GROUP BY saledate - Original Message - From: mayo [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Wednesday, June 22, 2005 6:29 PM Subject: group by date, missing something I'm trying to do a group by date on an access db. I have a list of sales and want to group by month and year. I can't get even a test run to work SQL below: SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal FROM sales GROUP by theMonth Thx -- gil ~| 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:210302 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: group by date, missing something
This will work, but it will group by dates down to the second. So that means the query won't return any grouped records unless they actually occurred on the exact same second. You need to use DATEPART() or a similar function, to group by the Year and the Month such as MM. That is the only way you can really group the results correctly. M!ke From: eric.creese [mailto:[EMAIL PROTECTED] Sent: Wed 6/22/2005 6:34 PM To: CF-Talk Subject: Re: group by date, missing something SELECT sum(saleTotal), saledate FROM sales GROUP BY saledate - Original Message - From: mayo [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Wednesday, June 22, 2005 6:29 PM Subject: group by date, missing something I'm trying to do a group by date on an access db. I have a list of sales and want to group by month and year. I can't get even a test run to work SQL below: SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal FROM sales GROUP by theMonth Thx -- gil ~| 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:210304 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: group by date, missing something
Thanks eric, but that gives a total for every day. June 21 10,000 June 20 9,555 June 19 11,200 That's useful but I'm trying to get the total sales for each month and need to do a group by for the month June ... 250,000 May ... 430,000 Apr ... 380,000 It should be something like: SELECT monthpart(datefield) AS xyz But it's not working. I'm wondering if I'm missing something that is Microsoft Access specific. (client is using access, can't change that) :( Thx -Original Message- From: eric.creese [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 22, 2005 7:35 PM To: CF-Talk Subject: Re: group by date, missing something SELECT sum(saleTotal), saledate FROM sales GROUP BY saledate - Original Message - From: mayo [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Wednesday, June 22, 2005 6:29 PM Subject: group by date, missing something I'm trying to do a group by date on an access db. I have a list of sales and want to group by month and year. I can't get even a test run to work SQL below: SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal FROM sales GROUP by theMonth Thx -- gil ~| 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:210306 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: group by date, missing something - SOLVED, thx
Thx barneyb for the hint: The solution is: SELECT MONTH(saleDate) AS theMonth, YEAR(saleDate) AS theYear, sum(total) as dailyTotal FROM sale GROUP by MONTH(saleDate), YEAR (saleDate) ORDER BY YEAR(saleDate), MONTH(saleDate) -- thx, gil -Original Message- From: mayo [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 22, 2005 9:52 PM To: CF-Talk Subject: RE: group by date, missing something Thanks eric, but that gives a total for every day. June 21 10,000 June 20 9,555 June 19 11,200 . That's useful but I'm trying to get the total sales for each month and need to do a group by for the month June ... 250,000 May ... 430,000 Apr ... 380,000 . It should be something like: SELECT monthpart(datefield) AS xyz But it's not working. I'm wondering if I'm missing something that is Microsoft Access specific. (client is using access, can't change that) :( Thx -Original Message- From: eric.creese [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 22, 2005 7:35 PM To: CF-Talk Subject: Re: group by date, missing something SELECT sum(saleTotal), saledate FROM sales GROUP BY saledate - Original Message - From: mayo [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Wednesday, June 22, 2005 6:29 PM Subject: group by date, missing something I'm trying to do a group by date on an access db. I have a list of sales and want to group by month and year. I can't get even a test run to work SQL below: SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal FROM sales GROUP by theMonth Thx -- gil ~| 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:210307 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: Group Vacation Calendar?
It's setup using fusebox, but can be modified pretty easily http://cfopen.org/projects/coldcalendar/ -Original Message- From: Claremont, Timothy [mailto:[EMAIL PROTECTED] Sent: Friday, April 15, 2005 9:12 AM To: CF-Talk Subject: Group Vacation Calendar? Anybody got the workings for a simple vacation calendar for my intranet? Just need a place where people can indicate their vacation times for the year. TIA Tim ** 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 delete it from your system. This footnote also confirms that this email message has been swept for the presence of computer viruses. Thank You, Viahealth ** ~| 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:203033 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: GROUP and ORDER Conundrum
you need to add an ORDER BY clause to your query:
ORDER BY group, displayorder
('group' is a reserved word, btw...if that really is currently your
column name you might want to look into changing that)
On Thu, 03 Feb 2005 21:10:35 -0500, Les Mizzell [EMAIL PROTECTED] wrote:
I have records in a SQL Server database that I'm displaying by GROUP -
but need to order stuff within each group as well.
I have a column for the GROUP and another column for the DISPLAY ORDER
so the client can order the specific items as he wishes.
Let's say there's currently five items in the database. If he adds a NEW
RED ITEM after the other items in the database, I end up with the
display doing the below - but I need the RED items to stay together and
in order.
RED
red thing 1
red thing 2
red thing 3
BLUE
blue thing 1
blue thing 2
RED
red thing 4
I've got no problems arranging the order without the GROUPS, and I've
got no problem display the GROUPS without the order. Getting the two to
work together is giving me a fit...
Advice?
~|
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:193032
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: GROUP and ORDER Conundrum
Show us your SQL code. What does your ORDER BY look like? Ade -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: 04 February 2005 02:11 To: CF-Talk Subject: GROUP and ORDER Conundrum I have records in a SQL Server database that I'm displaying by GROUP - but need to order stuff within each group as well. I have a column for the GROUP and another column for the DISPLAY ORDER so the client can order the specific items as he wishes. Let's say there's currently five items in the database. If he adds a NEW RED ITEM after the other items in the database, I end up with the display doing the below - but I need the RED items to stay together and in order. RED red thing 1 red thing 2 red thing 3 BLUE blue thing 1 blue thing 2 RED red thing 4 I've got no problems arranging the order without the GROUPS, and I've got no problem display the GROUPS without the order. Getting the two to work together is giving me a fit... Advice? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005 ~| 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:193033 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: Group By Question
I have a query that currently looks like: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select * from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email ORDER BY email_blast.embl_companyname /CFQUERY I always get an error that the other fields aren't contained in an aggregate function. How can I get this query to Group By just the embl_email field? I used the group property in my cfoutput which does group all the emails but I need to get a count on the unique emails that clicked through and not every email. Just list the other selected fields after email_blast.embl_email. You should get the aggregation you want that way. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Group By Question
Dave, That doesn't seem to do what I want it to. I modified the query to grab just the fields I'm using and grouped by those fields: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam e,email_results.emres_prod_part_numl,email_results.emres_click_date from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam e,email_results.emres_prod_part_numl,email_results.emres_click_date Order By email_blast.embl_companyname /CFQUERY I still get duplicate data so if person from companyA clicked twice on a product the query will show companyA [EMAIL PROTECTED] companyA [EMAIL PROTECTED] I want companyA to show up only once even if they clicked 5 times. Does that make sense? Ben -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 25, 2003 11:43 AM To: CF-Talk Subject: RE: Group By Question I have a query that currently looks like: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select * from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email ORDER BY email_blast.embl_companyname /CFQUERY I always get an error that the other fields aren't contained in an aggregate function. How can I get this query to Group By just the embl_email field? I used the group property in my cfoutput which does group all the emails but I need to get a count on the unique emails that clicked through and not every email. Just list the other selected fields after email_blast.embl_email. You should get the aggregation you want that way. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Group By Question
Why are you using GROUP BY if you dont have any aggregate functions in your select clause (ie AVG() SUM() etc). Try taking it out -Original Message- From: Ben Densmore [mailto:[EMAIL PROTECTED] Sent: 25 November 2003 16:45 To: CF-Talk Subject: RE: Group By Question Dave, That doesn't seem to do what I want it to. I modified the query to grab just the fields I'm using and grouped by those fields: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam e,email_results.emres_prod_part_numl,email_results.emres_click_date from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam e,email_results.emres_prod_part_numl,email_results.emres_click_date Order By email_blast.embl_companyname /CFQUERY I still get duplicate data so if person from companyA clicked twice on a product the query will show companyA [EMAIL PROTECTED] companyA [EMAIL PROTECTED] I want companyA to show up only once even if they clicked 5 times. Does that make sense? Ben -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 25, 2003 11:43 AM To: CF-Talk Subject: RE: Group By Question I have a query that currently looks like: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select * from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email ORDER BY email_blast.embl_companyname /CFQUERY I always get an error that the other fields aren't contained in an aggregate function. How can I get this query to Group By just the embl_email field? I used the group property in my cfoutput which does group all the emails but I need to get a count on the unique emails that clicked through and not every email. Just list the other selected fields after email_blast.embl_email. You should get the aggregation you want that way. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Group By Question
use MAX(columnname) as alias along with your grouping. dave - Original Message - From: Ben Densmore To: CF-Talk Sent: Tuesday, November 25, 2003 11:44 AM Subject: RE: Group By Question Dave, That doesn't seem to do what I want it to. I modified the query to grab just the fields I'm using and grouped by those fields: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam e,email_results.emres_prod_part_numl,email_results.emres_click_date from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam e,email_results.emres_prod_part_numl,email_results.emres_click_date Order By email_blast.embl_companyname /CFQUERY I still get duplicate data so if person from companyA clicked twice on a product the query will show companyA [EMAIL PROTECTED] companyA [EMAIL PROTECTED] I want companyA to show up only once even if they clicked 5 times. Does that make sense? Ben -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 25, 2003 11:43 AM To: CF-Talk Subject: RE: Group By Question I have a query that currently looks like: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select * from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email ORDER BY email_blast.embl_companyname /CFQUERY I always get an error that the other fields aren't contained in an aggregate function. How can I get this query to Group By just the embl_email field? I used the group property in my cfoutput which does group all the emails but I need to get a count on the unique emails that clicked through and not every email. Just list the other selected fields after email_blast.embl_email. You should get the aggregation you want that way. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Group By Question
That doesn't seem to do what I want it to. I modified the query to grab just the fields I'm using and grouped by those fields: CFQUERY name=clicks_per_product_market_display datasource=# Data_Source # select email_blast.embl_email,email_blast.embl_companyname,email_blas t.embl_name,email_results.emres_prod_part_numl,email_results.emre s_click_date from email_results, email_blast Where email_results.emres_blast_sent_id = #URL.BLAST# AND email_results.emres_prod_catagory = '#get_cat_info.Category#' AND email_blas_id = emres_blast_email_id Group By email_blast.embl_email,email_blast.embl_companyname,email_blas t.embl_name,email_results.emres_prod_part_numl,email_results.emre s_click_date Order By email_blast.embl_companyname /CFQUERY I still get duplicate data so if person from companyA clicked twice on a product the query will show companyA [EMAIL PROTECTED] companyA [EMAIL PROTECTED] I want companyA to show up only once even if they clicked 5 times. Does that make sense? OK. I read your SQL a little more closely this time; I apologize for not reading it more accurately before. You can only effectively use the GROUP BY clause when you use aggregate functions within your SELECT statement: SELECT COUNT(email_blast.embl_email) FROM ... WHERE ... GROUP BY email_blast.embl_email It looks like what you really want is to select all the data without GROUP BY, and use the GROUP attribute of CFOUTPUT to display your data: cfoutput query=qWhatever group=embl_email #embl_companyname# cfoutput ... repeated data for that company ... /cfoutput /cfoutput Alternatively, if you just want to select each item once, you can use DISTINCT within your query: SELECT DISTINCT embl_email ... I'm not sure exactly which outcome you want, though. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: GROUP BY year, month loop
Is the problem with your query or your output? Do you get the right results when you run the query in some sort of query analyzer? If not, my first question would be about the nested year(month( function. Do you need to do it that way? Can't you just use year(column)? If it's an output issue, it's probably because of the way you're grouping your cfoutput ordering your query. You're grouping by the month, but you don't then have an inner cfoutput to get all the years. If you want to display by month, you should also order your query by month, then by year. Right now, I'm guessing that your query is ordering by year, then by month (based on the order of your select and group by clauses.) - Original Message - From: Ihrig Paul E Cont 88 ABW/EM [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 9:00 AM Subject: GROUP BY year, month loop ok i am having a slight brain fart. have left my reference books at home i want to loop through year, grouping by month displaying sum total. right now i have it grouping by month displaying sum total, but it lumps the year together.. should be simple, and i have search archives.. thanks -paul cfquery name=rsDisMonth datasource=recycle SELECT Year(Month(Offload_Date)) as yearEnterd, Month(Offload_Date) as monthEnterd, SUM(Offload_Gallons) AS SumGal FROM tblOffload GROUP BY Year(Month(Offload_Date)), Month(Offload_Date) /cfquery cfoutput query=rsDisMonth group=monthEnterd tr align=right valign=top td#YearEnterd#/td td#monthEnterd#/td td#LSNumberFormat(SumGal,',99.99')#/td tdnbsp;/td tdnbsp;/td tdnbsp;/td /tr /cfoutput ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: GROUP BY year, month loop
hey Deanna. i figured it out.. was just the morning fog... thanks.. -paul cfquery name=rsDisMonth datasource=recycle SELECT Year(Offload_Date) as yearEnterd, Month(Offload_Date) as monthEnterd, SUM(Offload_Gallons) AS SumGal FROM tblOffload GROUP BY Year(Offload_Date), Month(Offload_Date) /cfquery !--- Start our first part of a loop to order the results by the year they where enterd. --- cfoutput query=rsDisMonth group=yearEnterd tr td colspan=6strongTotal Sum for year #YearEnterd#/strong/td /tr !--- Our second part of the loop orders by the month as well as adds up the total gallons shiped out. --- cfoutput group=monthEnterd tr td align=right valign=topnbsp;/td td align=right valign=top#MonthAsString(monthEnterd)#/td td align=right valign=top#LSNumberFormat(SumGal,',99.99')#/td td align=right valign=topnbsp;/td td align=right valign=topnbsp;/td td align=right valign=topnbsp;/td /tr /cfoutput /cfoutput ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Group and CurrentRow MOD
I'm trying to group people by state, with two columns of names under each state heading. This works, other than some states don't always fill the second top column in the group. Some rows display correctly, and others don't. I have some states with 3 names that work correctly. I get STATE name1 name2 STATE name1 name2 name3 etc. How can I force the 2nd column to fill from the top of the group. I'm not sure if this will help or not, but you can check out my cf_recordcount tag. http://www.twcreations.com/recordcount/ It's designed to be able to return the top X number of records or X number of records per page based on any group out of a joined query. -- Bud Schneehagen - Tropical Web Creations, Inc. _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Web Based Solutions / eCommerce Development Hosting http://www.twcreations.com/ - http://www.cf-ezcart.com/ 954.721.3452 - Toll Free: 877.207.6397 - Fax: 954.721.7493 ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Group and CurrentRow MOD
You can't really use currentrow with group - it throws it off. Best thing to do in those cases is create your own counter, that you refresh each time the group is looped on, and use that to do MODs on. That said, to do two columns the way you want, the manual counter bit won't work either. I'd suggest either doing your cfoutputs the same way you are now, but beforehand, and keep count of how many rows are present for each state, and then when you're doing your display, you'll know at what point in your manual counter for each state you need to start a fresh column. Either that way, or do a ValueList() on your state field in your query, loop over it and keep counts of how many times each state appears in the list, and use that to determine when to start fresh columns. ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: group by in cfoutput
look at the group attribute of cfoutput. Should do what you want. HTH, Sam - Original Message - From: Dowdell, Jason G [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 2:30 PM Subject: group by in cfoutput Hi all, Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this ID Name ParentID Level 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. Any ideas? - Jason Dowdell IM-AES Web Developer 321.799.6845 [EMAIL PROTECTED] - ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: group by in cfoutput
It does a portion of what I want. I need to restrict the output of the group by to a max of 3 records per parent category. ~jason -Original Message- From: Sam Farmer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:11 PM To: CF-Talk Subject: Re: group by in cfoutput look at the group attribute of cfoutput. Should do what you want. HTH, Sam - Original Message - From: Dowdell, Jason G [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 2:30 PM Subject: group by in cfoutput Hi all, Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this ID Name ParentID Level 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. Any ideas? - Jason Dowdell IM-AES Web Developer 321.799.6845 [EMAIL PROTECTED] - ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: group by in cfoutput
Can you put maxrows=3 in the innermost CFOUTPUT? -Original Message- From: Dowdell, Jason G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:15 PM To: CF-Talk Subject: RE: group by in cfoutput It does a portion of what I want. I need to restrict the output of the group by to a max of 3 records per parent category. ~jason -Original Message- From: Sam Farmer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:11 PM To: CF-Talk Subject: Re: group by in cfoutput look at the group attribute of cfoutput. Should do what you want. HTH, Sam - Original Message - From: Dowdell, Jason G [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 2:30 PM Subject: group by in cfoutput Hi all, Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this ID Name ParentID Level 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. Any ideas? - Jason Dowdell IM-AES Web Developer 321.799.6845 [EMAIL PROTECTED] - ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: group by in cfoutput
Here is the secret language I was looking for... #ChildName[CurrentRow]# I needed to understand how the grouping actually worked in order to know what syntax I could use to access the CurrentIndex of the query. ~Jason -Original Message- From: Dowdell, Jason G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:15 PM To: CF-Talk Subject: RE: group by in cfoutput It does a portion of what I want. I need to restrict the output of the group by to a max of 3 records per parent category. ~jason -Original Message- From: Sam Farmer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:11 PM To: CF-Talk Subject: Re: group by in cfoutput look at the group attribute of cfoutput. Should do what you want. HTH, Sam - Original Message - From: Dowdell, Jason G [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 2:30 PM Subject: group by in cfoutput Hi all, Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this ID Name ParentID Level 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. Any ideas? - Jason Dowdell IM-AES Web Developer 321.799.6845 [EMAIL PROTECTED] - ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: group by in cfoutput
That's the piece I'm working on now. It won't allow you to do that since you must specify a query in order to specify maxrows. ~Jason -Original Message- From: Everett, Al [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:17 PM To: CF-Talk Subject: RE: group by in cfoutput Can you put maxrows=3 in the innermost CFOUTPUT? -Original Message- From: Dowdell, Jason G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:15 PM To: CF-Talk Subject: RE: group by in cfoutput It does a portion of what I want. I need to restrict the output of the group by to a max of 3 records per parent category. ~jason -Original Message- From: Sam Farmer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:11 PM To: CF-Talk Subject: Re: group by in cfoutput look at the group attribute of cfoutput. Should do what you want. HTH, Sam - Original Message - From: Dowdell, Jason G [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 2:30 PM Subject: group by in cfoutput Hi all, Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this ID Name ParentID Level 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. Any ideas? - Jason Dowdell IM-AES Web Developer 321.799.6845 [EMAIL PROTECTED] - ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: group by in cfoutput
Hi all, Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this IDNameParentIDLevel 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. You might try a sub-query ... something like ... SELECT parent.name, parent.id, child.name as childname, child.id as childid, child.parentid as child FROM mytable parent LEFT JOIN mytable child ON ( child.level = 2 and child.id in (SELECT TOP 3 id FROM mytable WHERE parentid = parent.id) ) WHERE parent.level = 1 GROUP BY parent.id, parent.name, child.parentid, child.id, child.name ORDER BY parent.name, child.name Then you should be able to use your cfoutput group on id and sub-cfoutput group on childid and come up with the 3 sub's you want... hth S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: group by in cfoutput
Even if you can, I suspect it's not a very efficient method. Can you put maxrows=3 in the innermost CFOUTPUT? -Original Message- From: Dowdell, Jason G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:15 PM To: CF-Talk Subject: RE: group by in cfoutput It does a portion of what I want. I need to restrict the output of the group by to a max of 3 records per parent category. ~jason -Original Message- From: Sam Farmer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 3:11 PM To: CF-Talk Subject: Re: group by in cfoutput look at the group attribute of cfoutput. Should do what you want. HTH, Sam - Original Message - From: Dowdell, Jason G [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 2:30 PM Subject: group by in cfoutput Hi all, Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this ID Name ParentID Level 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. Any ideas? - Jason Dowdell IM-AES Web Developer 321.799.6845 [EMAIL PROTECTED] - ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: group by in cfoutput
Dowdell, Jason G wrote: Here's a bit of a challenge. I have a single table storing category information similar to any search engine. The table structure looks like this IDNameParentIDLevel 1 Computers 0 1 2 Laptop 1 2 3 Desktop 1 2 Since I have to display this information I must join the table back to itself. All joining has gone fine :) My problem is in the display of the information to the users. I want to display the Parent category and then only 3 children from each category. Sounds easy but I'm not sure if I can do this in sql or not. If sql could accomplish this then super, if not then it's a good CF challenge. SELECT p.name AS parentname, c.name AS childname FROM category c INNER JOIN category p ON (c.parentID = p.ID AND p.level = 1) WHERE ( SELECT Count(*) FROM category WHERE category.parentID = c.parentID AND category.ID c.ID ) = 2; cfoutput query= group=parentname #parentname# cfoutput #childname# /cfoutput /cfoutput Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: group by date question
I'm trying to group my query results by day. The field I'm grabbing from has a date/time stamp in it so what I'm getting are results grouped by time. I tried to fix this with this code which obviously doesn't work. How can I group this by date and ignore the time stamp? cfquery name=contact_count_ttl datasource=#heinz.dsn# SELECT logdate, DATEPART(dy, logdate) as doy, COUNT(*) as count_ttl FROM customer_log WHERE logdate BETWEEN #start# AND #end# GROUP BY doy ORDER BY logdate /cfquery I remember somebody else on the list saying you could group by convert(datetime,convert(int,doy)) and that would convert the date to an integer representation of the day and then back to a datetime value and truncate the time from the date... Or maybe he was using CAST ... I don't remember precisely, but it sounded to me like a really elegant hack. :) In the past I've generally added a 2nd date column to the table and simply inserted the date from coldfusion discluding the time. Which from the db perspective is liable to be more efficient, although if the date column ever gets updated outside of CF you might wind up with a situation where the two date columns don't match. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: group by date question
It depends on the database, of course. Assuming you're using some version of SQL Server (or Sybase) you might want to try something like: SELECT logdate, convert(char(14), logdate, 111) as doy, COUNT(*) as count_ttl FROM customer_log WHERE logdate BETWEEN #start# AND #end# GROUP BY convert(char(14), logdate, 111) ORDER BY logdate Chuck McElwee Macromedia Certified Advanced ColdFusion Developer www.etechsolutions.com [EMAIL PROTECTED] -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 29, 2002 6:08 PM To: CF-Talk Subject: Re: group by date question I'm trying to group my query results by day. The field I'm grabbing from has a date/time stamp in it so what I'm getting are results grouped by time. I tried to fix this with this code which obviously doesn't work. How can I group this by date and ignore the time stamp? cfquery name=contact_count_ttl datasource=#heinz.dsn# SELECT logdate, DATEPART(dy, logdate) as doy, COUNT(*) as count_ttl FROM customer_log WHERE logdate BETWEEN #start# AND #end# GROUP BY doy ORDER BY logdate /cfquery I remember somebody else on the list saying you could group by convert(datetime,convert(int,doy)) and that would convert the date to an integer representation of the day and then back to a datetime value and truncate the time from the date... Or maybe he was using CAST ... I don't remember precisely, but it sounded to me like a really elegant hack. :) In the past I've generally added a 2nd date column to the table and simply inserted the date from coldfusion discluding the time. Which from the db perspective is liable to be more efficient, although if the date column ever gets updated outside of CF you might wind up with a situation where the two date columns don't match. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Group by on date/time field
In the select statement of your query you can use the SQL Server DatePart function as I copy and paste from the SQL Server help file, any questions let me know: -Begin Shameless Copying-- Datepart () Returns an integer representing the specified datepart of the specified date. Syntax DATEPART(datepart, date) Arguments datepart Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server. Datepart Abbreviations year yy, quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms --End Shameless Copying- Gregory Harris [EMAIL PROTECTED] -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Monday, July 22, 2002 1:59 PM To: CF-Talk Subject: Group by on date/time field cf_BrainFart I have a query that needs to be output in groups based on a datetime value (SQL Server 7.0/2000). The trouble I'm having is with the time value. I need to group on the date alone. Do I need to store the values seperately, or can SQL parse it in the query as only the date portion so CF can use it in the cfoutput group=? /CF_BrainFart Jeff Beer Director of Application Development Digital Stormfront, Inc http://www.digitalstormfront.com http://www.digitalstormfront.com __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Group by on date/time field
Thanks Gregory, I had tried that earlier, and kept getting errors - I was trying to add the DATEPART to the SQL group by as well.. doh! I ended up with SELECT ... DATEPART(dy,orders.orderdate) AS myDate ... then using myDate as the group= param in the query output - works like a charm.. Thanks! Jeff -Original Message- From: Gregory Harris [mailto:[EMAIL PROTECTED]] Sent: Monday, July 22, 2002 5:23 PM To: CF-Talk Subject: RE: Group by on date/time field In the select statement of your query you can use the SQL Server DatePart function as I copy and paste from the SQL Server help file, any questions let me know: -Begin Shameless Copying-- Datepart () Returns an integer representing the specified datepart of the specified date. Syntax DATEPART(datepart, date) Arguments datepart Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by MicrosoftR SQL ServerT. Datepart Abbreviations year yy, quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms --End Shameless Copying- Gregory Harris [EMAIL PROTECTED] -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Monday, July 22, 2002 1:59 PM To: CF-Talk Subject: Group by on date/time field cf_BrainFart I have a query that needs to be output in groups based on a datetime value (SQL Server 7.0/2000). The trouble I'm having is with the time value. I need to group on the date alone. Do I need to store the values seperately, or can SQL parse it in the query as only the date portion so CF can use it in the cfoutput group=? /CF_BrainFart Jeff Beer Director of Application Development Digital Stormfront, Inc http://www.digitalstormfront.com http://www.digitalstormfront.com __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
