Showing unique columns + Number of rows matching 1

[fergy1]

Hello-

I want to have a drop down that shows the cities in a listing database and the # of listings for that city in parenthesis next to it. So it would show "New York (5,000)" etc...

I have a table called Listings to pull from with a column named city. So how can I do that?

 

[gmmastros]

Select Listing,  
       Count(1) As ListCount
From   Listing
Group By Listing
Order By Listing

This query should return the data you are looking for. I recommend you format it in your front end app to appear with the count in parenthesis (and commas in apropriate places).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fergy1]

So if i wanted to include city in your example not just listing it would look like this?

Select City, ListingID, Count(1) as ListCount from tblListings Group By ListingID Order By City

 

[gmmastros]

I may have mis-understood (and may still).

Does this return what you want?

Select City,
       Count(1)
From   Listings
Group By City
Order By City

If not, I suggest you post a couple rows of sample data with expected results.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fergy1]

that one did it...Thanks!

 

[gmmastros]

You're welcome.
Thanks for the star.
Sorry about misunderstanding on the first go-round.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fergy1]

George-

One more question. should have had this originally but forgot about the merge. I get a Column 'N.CityId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. How can i fix this?

<select name="City"><%
set cityRst = GetFromDatabase ("Select c.CityID, n.CityId, n.CityName, Count(1) As ListCount From tblListings c, tblCities N where c.MLSTableID = '5' and c.CityID = n.CityID Group By c.CityID Order By CityName")		 Do While not(cityRst.EOF)%>
<option value="<%=cityRst("CityID")%>"><%=cityRst("CityName")%>(<%=CityRst("Listcount")%>)</option>
<% cityRst.MoveNext 
   Loop
   set cityRst = nothing %>
</select>

 

[gmmastros]

A couple things...

You only need to return the cityId once because you're joining them from seperate tables, forcing them to be the same.

try this...

Select c.CityID, n.CityName, Count(1) As ListCount From tblListings c, tblCities N where c.MLSTableID = '5' and c.CityID = n.CityID Group By c.CityID, n.CityName Order By CityName")

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fergy1]

You are a stud! Thanks man!

 

[gmmastros]

Thanks again.

Now, please, do me a favor. Do a google search on [!]"SQL Injection"[/!]. Take an hour or so to read through the first couple of responses from Google.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom