CODEWITHSUNDEEP
.netasp.netsqlsql-serverdatabase
Justin C
Justin C

Reputation: 1924

condense logic down to one Sql query

first is the disclaimer, I did not design the database I am now supporting and I would not have designed it this way, but here I am.

Problem: My client has a table in his database that contains listings for rentals. In this table is a text field to store the city of the listing. What my client is hoping to see is a dropdown list of all the unique cities with a number next to each one representing how many listings are in that city.

Ignoring for the time being spelling and capitalization issues, what Sql should I use to get the unique list along with each items count? For a proof of concept I queried for the distinct list of cities (easy enough), and then iterated through each one querying the number of times it is in the table. This is obviously horribly inefficient.

Can someone help me with the Sql to do all this in one statement?

Upvotes: 0

Views: 158

Answers (7)

Ervin
Ervin

Reputation: 2452

SELECT DISTINCT city_name, count(*)
FROM rentals
GROUP BY city_name

Upvotes: 1

Saar
Saar

Reputation: 8474

select CityName, COUNT(*) from CitiesTable

group by CityName

Upvotes: 2

Meff
Meff

Reputation: 5999

SELECT
 CityName,
 COUNT(*) AS CityListings
FROM
 YourTable
GROUP BY
 CityName
ORDER BY
 CityName

Upvotes: 3

paxdiablo
paxdiablo

Reputation: 881573

Do you mean something like the following?

select city, count(city) from listings
group by city

This should turn:

Shepparton blah blah blah
Mildura    yada yada yada
Shepparton dum  de   dum

into:

Shepparton 2
Mildura    1

which is what it sounds like you're after.

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

To start with, try this

SELECT City, Count(*)
FROM myTable
GROUP BY City

Upvotes: 1

Oded
Oded

Reputation: 499052

You are looking for the Group By clause:

SELECT city, COUNT(city)
FROM rentals
GROUP BY city
ORDER BY city

Upvotes: 1

TomTom
TomTom

Reputation: 62101

SELECT CITY, COUNT (*) FROM TABLE GROUP BY CITY

;) Get a good SQL Book, This is "SQL in 21 hours" level ;)

Upvotes: 2

Related Questions