title: pick latest date of each item and get the sum in sqlite
body: <p>I have a requirement where there are 10 items for each item there are 10 records that has <code>buy price</code> and <code>sell price</code>.</p>
  
  <p>Now my requirement is to for each item I need to take the latest date and calculate the buy and sell price... in the same way I need to do for all 10 items and finally sum the buy and sell of all items to get the overall sum... I need to do this whole thing in a single query..</p>
  
  <p>I can write queries and sub queries but have no idea on how to write such a complex query..</p>
  
  <p>Data as:</p>
  
  <pre><code>item    date        buy    sell
  
  item1   28/10/2014  10      10
  item1   27/10/2014  10      10
  item1   26/10/2014  10      10
  item2   28/10/2014  10      10
  item2   27/10/2014  10      10
  item2   26/10/2014  10      10
  item3   28/10/2014  10      10
  item3   27/10/2014  10      10
  item3   26/10/2014  10      10
  </code></pre>
  
  <p>my rewuirment is to get </p>
  
  <pre><code>item1   28/10/2014  10      10
  item2   28/10/2014  10      10
  item3   28/10/2014  10      10
  </code></pre>
  
  <p>and finally <code>totay buy=30</code> and <code>total sell=30</code></p>
  
  <p>What I have tried till now:</p>
  
  <p>below query I have tried</p>
  
  <pre><code>select item as c, buy as a ,sell as b,date from table group by item order by  substr(date,7,4)||substr(date,4,2) ||substr(date,1,2) desc limit 1
  </code></pre>
  
  <p>This query returning value of only one 1 row corresponding to one item not 3 rows. I am not sure how to construct these kind of queries.</p>