Query That Averages Rent Based off of Bedrooms 1

[3dColor]

I have the following query that works fine when providing me with the average rent for the entire metro area:

<!--- 135 is Denver ---> 
<cfset cs = 135>

<cfquery name="avgRentpermonth" datasource="#DSN#">
  SELECT AVG(rentpermonth) as rentavg
  FROM	listing
  WHERE	metroFk = <cfqueryparam value='#cs#' cfsqltype='CF_SQL_INTEGER'>
</cfquery>

However I would like the query to return more detailed information from my table.

The rentals I have in my 'listing' table include the following variations for the 'bedrooms' field:
bedrooms1="1"
bedrooms2="2"
bedrooms3="3"
bedrooms4="4"
bedrooms6="Studio"

What I would like the query to return average rental prices for each of the bedroom types.

How do I write the query this way?

Thanks!
Dave

 

[darrellblackhawk]

add a group on bedrooms clause

 

[3dColor]

Thanks! That worked great.

 

[3dColor]

OK, I can cfdump the query and see all the values perfectly:

Query Data - query
BEDROOMS RENTAVG
1 1 743.1111
2 2 981.0529
3 3 1419.8049
4 4 2075.9310
5 5 3376.3200
6 6 779.5000

But I have no idea how to output these values like they are in the cfdump.

I tried this but it doesn't work:

<cfset cs = 135>

<cfquery name="avgRentpermonth" datasource="#DSN#">
	SELECT		bedrooms, AVG(rentpermonth) as rentavg
	FROM		listing
	WHERE		metroFk = <cfqueryparam value='#cs#' cfsqltype='CF_SQL_INTEGER'>
	GROUP BY 	bedrooms
</cfquery>

<cfdump var="#avgRentpermonth#" label = "Query Data">

<cfoutput>
#DollarFormat(Round(avgRentpermonth.rentavg))#
</cfoutput>

<cfoutput query="avgRentpermonth" group="bedrooms">
#avgRentpermonth.bedrooms#<br>
</cfoutput>

 

[darrellblackhawk]

I'm unfamiliar with Cold Fusion, so someone else will need to help with this.

 

[3dColor]

That's ok, i figured it out this morning.