title: Can I eliminate subqueries when calculating multiple aggregates in SQLite?
body: <p>I'm currently looking at a ratings database in SQLite 3.8.7.1 (Debian Jessie) and I'm having some diffculty getting the aggregates I want from a single query to return to a web app.</p>
  
  <p>Dredging through my memories of SQL classes at uni and plenty of documentation, I've got a query that finds the total rating, plus positive and negative counts for each item being rated, but I'm not convinced I've got a sensible way of doing this, or that it's going to be particularly efficient.</p>
  
  <p>The bare bones are on <code>sqlfiddle.com</code> as <a href="http://www.sqlfiddle.com/#!7/a71acb/1" rel="nofollow noreferrer"><code>http://www.sqlfiddle.com/#!7/a71acb/1</code></a></p>
  
  <p>Essentially I have an item key, then the username of the person rating it, a language field currently set to <code>NULL</code> in all cases, date of the rating and either <code>1</code> or <code>-1</code> in the value field. I want to return a table of all items, with columns for number of positive ratings, number of negative ratings and overall rating (positive less negative).</p>
  
  <p>Can I remove the subqueries somehow or make them more efficient? Are there alternative approaches I can pursue in SQLite?</p>
  
  <p>Actually, my query returns a negative number for the total negative ratings; can I make this positive?</p>