CODEWITHSUNDEEP
sqlite
tmighty
tmighty

Reputation: 11425

Retrieve number of occurances in sqlite table

I have a table that has 2 fields:

The street name can occur multiple times like this:

Hauptstraße   |    Frankfurt am Main
Hauptstraße   |    Berlin
Hauptstraße   |    München
Kirchweg      |    Frankfurt am Main
Kirchweg      |    Düsseldorf
Bahnhofstraße |    Köln
Bahnhofstraße |    München

I would like to retrieve all street names (only once) and find out how often they occur.

In my example, I would like to receive the following:

Hauptstraße | 3

Kirchweg | 2

Bahnhofstraße | 2

I have tried the following SQLite statement, but it didn't do what I need:

"SELECT thename, SUM(thename) AS mysum FROM streets ORDER BY mysum DESC"

It returns a single recordset with a weird number.

How could I achieve what I want to do?

Thank you.

Upvotes: 0

Views: 36

Answers (1)

benjessop
benjessop

Reputation: 1969

SELECT thename, COUNT(thename) AS mysum FROM streets GROUP BY thename ORDER BY mysum DESC;

Upvotes: 1

Related Questions