Grouping question

Eric Bourland
Eric Bourland used Ask the Experts™
ColdFusion 9
MS SQL Server 2005

Hi. My CFOUTPUT does not group on column PublicationYear the way that I think it should. =) How can I edit my query or my output to get this result:

"CEP scheduled no meetings in year 2011."

rather than this result:

"CEP scheduled no meetings in year 20112011201120112011201120112011201120112011201120112011201120112011."

I am guessing I cannot group variable #url.selectedYear# on column PublicationYear because variable #url.selectedYear# is not referenced in query getDocumentYears.

Thanks as always for advice. I've been puzzling over this task for a while today.


Query, getDocumentYears:

<!--- query to select document publication years --->
<cfquery name="getDocumentYears" datasource="#application.datasource#">
SELECT DISTINCT Year(DocumentPublicationDate) AS PublicationYear
FROM tbl_CEP_Documents
ORDER BY PublicationYear DESC


<p>CEP scheduled no meetings in year <cfoutput query="getDocumentYears" group="PublicationYear" groupcasesensitive="no">#url.selectedYear#</cfoutput>.</p>


Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
<cfoutput query="getDocumentYears" ....

I think that's the wrong query.  IIRC your page has 2 queries.  One that lists ALL available years. The other lists all documents for the #url.selectedYear#.  I think you should be using the 2nd query instead.  But use the recordCount to determine if any documents were found

<cfif getDocuments.recordCount eq 0>
     CEP scheduled no meetings in year #url.selectedYear# ....


That was it. I was wondering if it might be the other query. This worked:

<cfif ...>

<cfelseif getDocumentsandFiles.recordCount eq 0>

<p>CEP scheduled no meetings in year <cfoutput>#url.selectedYear#</cfoutput>.</p>



Thank you as always, _agx_. Hope you are well.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial