title: select list of albums of artist and display for every album tracks count which have requested artist and album in list
body: <p>I need to make complicated query.
  I have table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS tracks
       ( id INTEGER PRIMARY KEY, path TEXT,
       title TEXT, artist TEXT,
       album TEXT, genre TEXT, 
      duration INTEGER, rating INTEGER );
  </code></pre>
  
  <p>Sory for dirty title of question but i don't understand how to explain it more shortly.</p>
  
  <p>Question:
  How to display list of albums of requested artist and for each displayed album calculate count of tracks which have requested artist in current album in single query.</p>
  
  <p>I have tried something like this:</p>
  
  <pre><code>SELECT albtbl.album as album, albtbl.artist as artist, 
  ( SELECT count(*) FROM trackstbl WHERE trackstbl.artist = albtbl.aritst ) as tracksCount 
  FROM ( SELECT tbl.album as album, tbl.artist as artist, count(title) as tracksCount FROM tracks as tbl  WHERE tbl.artist = 'requested_artist'
   GROUP BY tbl.album ) as albtbl, FROM tracks as trackstbl ORDER BY album ASC
  </code></pre>
  
  <p>but it not compiled:</p>
  
  <blockquote>
    <p>SQLiteException: near "FROM": syntax error:</p>
  </blockquote>