sqlite - Complex query on sql lite xcode

Question

I'm developing an iOS app and I have a sqlite database with 2 tables related by 1-to-many relationship.

Now I would like to do a query that retrieve all element by first table and in the same time do a count by second table so I can pass the result into my view.

CREATE TABLE track(
    trackid     INTEGER, 
    trackname   TEXT, 
    trackartist INTEGER,
    FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
);

I would like to create a query that returns all artist name and the count of track for each artist name so I can pass this value to my list.

Is it possible? Any help? Thanks to Joe, your code works well for my, but it's possibile to add new field for store the result of count?

Sorry and if i would take the also all trackname for each artist in the same query?

Answer 1

SELECT a.artistname, count(*)
FROM track t
INNER JOIN artist a
   on  t.trackartist = a.artistid
GROUP BY a.artistid