title: postgreSQL Duplicate rows counting on join
body: <p>I have one complicated question. I'll try to explain it with example:</p>
  
  <p>have one table that have primary key, and I want to join other table there the first's table primary key is foreign key, and I want If in the second table there is duplicate foreign key to select the number of repeatability. For example:</p>
  
  <p>1-st table:</p>
  
  <pre><code>  id    name 
    ---  -----
    1     Greg
    2     Alan 
    3    George 
    4     John 
    5     Peter   
  </code></pre>
  
  <p>2-nd table</p>
  
  <pre><code> id       aid        data   
   ---     -----      -------
   1        2          CCCV   
   2        2          VVVV 
   3        3          DDDDD 
   4        3          SSSS 
   5        4          PPPPP 
  </code></pre>
  
  <p>I want the result of the join to be:</p>
  
  <pre><code> id(1st table)  aid   name    Data   Number
   -----------    ----  -----   -----  -----
   1               null  Greg    null   1
   2                1    Alan    CCCV   1
   2                2    Alan    VVVV   2
   3                3    George  DDDDD  1
   3                4    George  SSSS   2
   4                5    John    PPPPP  1
   5               null  Peter   null   1
  </code></pre>
  
  <p>I searched a lot, I couldn't find anything. Maybe I do not know how search, or there is no such thing as what I want to do.</p>