sql join two tables and get cumulative count
<p>I have the following two tables, </p>

<p>table 1:</p>

<pre><code>id    document
--------------
A3  B2 
A3  B400 
A5  B100 
A5  B500   
A7  B200 
A8  B6 
A8  B2 
A8  C1    
A8  B3 
</code></pre>

<p>table 2:</p>

<pre><code>id    name
--------------
A1  Jack 
A2  Martin 
A3  Jack 
A4  Thomas 
A5  Jack 
A6  Thomas 
A7  Thomas 
A8  John 
A9  John 
A10 Kate 
</code></pre>

<p>my filter is the following that should compare the <code>document</code> column and filter based on this list:</p>

<pre><code>WHERE table1.document IN (B2,B400,B100,B500,B200,B6,B2,B3)
</code></pre>

<p>the result should be like:</p>

<pre><code>name    freq
--------------
Jack    4
Thomas  1
John    3
</code></pre>

<p>explanations:
First I filter the documents I have in my <code>WHERE IN</code> clause. Then I look at the remaining documents' corresponding value of <code>id</code> column in <strong>table1</strong>, and 
look that <code>id</code> up in the second table, first column, and get the corresponding <code>name</code> for that id and put it in my results table.<br>
For example, A3 is repeated twice, and its corresponding <code>name</code> is <code>Jack</code>, so upto now Jack has the value of 3 in <code>freq</code> column, but since the next id in 
my table1 is A5, repeated twice, which happens to have <code>Jack</code> as its name also, so then <code>jack</code> will have 4 as the frequency.<br>
For the next value in table1, A7 has a corresponding name of <code>Thomas</code>, since A7 is repeated once, and no other id whose corresponding name is Thomas
is not in my list of table1, then Thomas will have freq of 1 ( I don't have A4, nor A6 in table1).<br>
And in the final example, I have A8 repeated 4 times in table1, but, since my list in <code>WHERE IN</code> doesn't have <code>C1</code> included, so that entry will be filtered
and not counted, so I have three A8 left, whose name is <code>John</code> and then it gets the value of 3.<br></p>

<p><strong>here is a <a href="http://sqlfiddle.com/#!9/70d64e" rel="nofollow noreferrer">demo</a> data.</strong></p>