title: SQL subqueries to count on multiple conditions
body: <p>I've table like this VB(name char(25), amount numeric(6), ret_date date):</p>
  
  <blockquote>
  <pre><code>Name  Amount  Ret_date
  ----  ------  --------
  abc    500    2011-01-21
  def    200    2011-01-20
  ghi   1000    
  xyz    800    2011-01-22
  def    200    
  def    400    2011-02-01
  pqr   2100    2011-02-12  
  abc   3000    
  abc   4100
  xyz    700    2011-01-22
  </code></pre>
  </blockquote>
  
  <p>What I wanted is (count based on ret_date for distinct names: total count, return count, pending count): </p>
  
  <pre><code>Name    tc  rc  pc 
  abc     3   1   2 
  def     3   2   1 
  xyz     2   2   0 
  ghi     1   0   1 
  pqr     1   1   0
  </code></pre>
  
  <p>I've tried the following queries, nothing worked! </p>
  
  <pre><code>SELECT vb.name, COUNT(*) tc, r.rc, p.pc FROM vb, ;
   (SELECT name, COUNT(*) rc FROM vb WHERE ret_date != {} group BY name) r, ;
   (SELECT name, COUNT(*) pc FROM vb WHERE ret_date = {} group BY name) p ;
   GROUP BY vb.name ORDER BY tc desc
  SELECT vb.name, COUNT(*) tc, r.rc, p.pc FROM vb ;
    LEFT JOIN (select name, COUNT(*) rc FROM vb WHERE ret_date != {} group BY name) r ON r.name = vb.name ;
    LEFT JOIN (select name, COUNT(*) pc FROM vb WHERE ret_date = {} group BY name) p ON p.name = vb.name ;
    GROUP BY vb.name
  SELECT vb.name, COUNT(*) tc, r.rc, p.pc FROM vb ;
    LEFT JOIN (select r.name, COUNT(*) rc FROM vb r WHERE ret_date != {} group BY name) r ON r.name = vb.name ;
    LEFT JOIN (select p.name, COUNT(*) pc FROM vb p WHERE ret_date = {} group BY name) p ON p.name = vb.name ;
    GROUP BY vb.name
  </code></pre>
  
  <p>I was able to get the count with 3 separate queries (total count, count for ret_date = {}, ret_date != {}) but can't get at the same time using a single query. Please help me get the desired output. I am new to SQL and can understand the basics but things like joins, subqueries are still difficult to understand! It would be helpful if you could explain what your query does.</p>
  
  <p>Note: I'm using MS Visual Foxpro 9.0 (Windows 7). <em>{ }</em> is a notation used to express emptiness for date type in Foxpro and <em>!=</em> is not equal to operator.</p>
  
  <p>Thanks a lot.</p>