title: How to get count of postitive and negative numbers per ID?
body: <p>I'd like to get a count of negative values and positive values for each id.</p>
  
  <p><a href="http://sqlfiddle.com/#!4/db410" rel="nofollow">Sample Fiddle</a></p>
  
  <p><code>ID=1 has 2 positive and 0 negative transactions. etc.</code></p>
  
  <pre><code> with trans_detail as 
   (
   select 1 as trans_id, 100 as trans_amount  from dual union all
   select 1 as trans_id, 200 as trans_amount  from dual union all
   select 2 as trans_id, -100 as trans_amount  from dual union all
   select 2 as trans_id, -300 as trans_amount  from dual union all
   select 3 as trans_id, 400 as trans_amount   from dual union all
   select 3 as trans_id, -500 as trans_amount  from dual
   )
  
   select trans_id,
         count(*) over (partition by trans_id) as pos_count,
         count(*) over (partition by trans_id) as neg_count        
  from trans_detail
  where trans_amount &gt; 0
  UNION
  select trans_id,
         count(*) over (partition by trans_id) as pos_count,
         count(*) over (partition by trans_id) as neg_count        
  from trans_detail
  where trans_amount &lt; 0;
  </code></pre>
  
  <p>Desired Result:</p>
  
  <pre><code> ID   POS_COUNT   NEG_COUNT
  ---- ----------- -----------
   1    2           0 
   2    0           2
   3    1           1
  </code></pre>