Need help with GROUP BY part of query

[3dColor]

I am having problems with the "GROUP BY" part in the query below. I would like the query to group the results so Coldfusion can email all the property alerts within a metro area or areas.

Instead of getting all the alerts in to one email, I am getting one alert per email (see the results below).

As you can see "Demo Dan" has signed up for alerts in metro area #132 and #135, while "Betsy Beta" has signed up just for #132.

So what I want to happen is "Demo Dan" to receive one email with the two properties from #132 and one property from #135, while "Betsy Beta" receives one email with the two properties from #132.

I tried grouping several different ways using just the GROUP BY line just above the ORDER BY line, but no success. Any ideas?

Here is the current output of your query:

-- Nathan's Query --
5 Alerts to send out for yesterday's ads = 7, 4, 4, 6, 6,
People wanting alerts for those ads = 1-Demo Dan, 1-Demo Dan, 1-Demo Dan, 2-Besty Beta, 2-Besty Beta,

1-Demo Dan - dan@demo.com
MetroFk=135 - 5754 S Lowell Way, $1,360

1-Demo Dan - dan@demo.com
MetroFk=132 - 22 Rose St., $1,375

1-Demo Dan - dan@demo.com
MetroFk=132 - 831 Meeker Street #600, $1,250

2-Besty Beta - betsy@beta.com
MetroFk=132 - 22 Rose St., $1,375

2-Besty Beta - betsy@beta.com
MetroFk=132 - 831 Meeker Street #600, $1,250

Here is the code that creates the above ouput:

<!--- send Alert emails for those signed up for them --->

<cfset today = CreateODBCDate(Now())>
<cfset yesterday = CreateODBCDate(DateAdd('d', -1, #today#))>

<!--- Nathan's --->
<CFQUERY NAME="Nathan" datasource="#DSN#">
	SELECT 		ListingParameter.listingBeginDate, ListingParameter.listingFK, ListingParameter.status,
				Listing.listingId, Listing.address, Listing.propType, Listing.cityFk, Listing.metroFk,
				Listing.rentpermonth, Listing.bedrooms, Listing.bathrooms, 
				Alerts.acctFk, Alerts.metroFk, Alerts.alertId,
				Account.acctId, Account.email, Account.nameFull
	FROM		Listing INNER JOIN ListingParameter ON Listing.ListingId = ListingParameter.listingFk 
     			INNER JOIN Alerts ON Listing.metroFK = Alerts.metroFk 
     			INNER JOIN Account ON Alerts.acctFk = Account.acctId 
	WHERE 		listingBeginDate = #yesterday# AND status = '1'
	ORDER BY 	Alerts.acctFk
</CFQUERY>

<h2>-- Nathan's Query --</h2>
<cfoutput><strong>#Nathan.recordcount#</strong></cfoutput>
Alerts to send out for yesterday's ads = <cfoutput query="Nathan"> <strong>#alertId#</strong>, </cfoutput><br>
People wanting alerts for those ads = <cfoutput query="Nathan"> <strong>#acctFk#-#nameFull#</strong>, </cfoutput>

<!--- This loop to be replaced with CFMail after testing --->
<cfloop query="Nathan">
	<p>
	<cfoutput><strong>#acctFk#-#Nathan.nameFull# - #Nathan.email#</strong></cfoutput><br>
	<cfoutput><strong>MetroFk=#Nathan.metroFk#</strong> - #Nathan.address#, $#NumberFormat(Nathan.rentpermonth)#<br></cfoutput>
  </p>
</cfloop>

 

[3dColor]

Nathan who the query is named after helped and said I could do it with Coldfusion in the following way:

<cfoutput query="Nathan" group="acctFk"> 
     <strong>#acctFk#-#Nathan.nameFull# - #Nathan.email#</strong><br/> 
     <cfoutput> 
     <strong>MetroFk=#Nathan.metroFk#</strong> - #Nathan.address#, $#NumberFormat(Nathan.rentpermonth)#<br> 
     </cfoutput> 
</cfoutput>