title: mysql query from 1 table that return sum of same column which different condition
body: <p>I have 1 transaction tables with columns quantity.  The table looks like this</p>
  
  <pre><code>item     quantity
  1        10
  1        15
  1       -10
  2        10
  3         5
  3       -10
  </code></pre>
  
  <p>The quantity is + means buy and - means sell.  I want to count the total sum of quantity for both buy and sell group by item_id in 1 query.</p>
  
  <p>The result should be like this:</p>
  
  <pre><code>item      buy      sell
  1         25       -10
  2         10         0
  3          5        10
  </code></pre>
  
  <p>I tried to do query like this:</p>
  
  <pre><code>SELECT item, SUM(quantity&gt;0) as buy, SUM(quantity&lt;0) as sell FROM table GROUP BY item
  </code></pre>
  
  <p>But it gives back count of rows instead of the sum of quantity.  Any idea how to do this in 1 query?  Many thanks!</p>
  
  <p>PS: I know I have to incorporate IF NULL to get item 2 return 0 instead null.</p>