Case when with group by in SQL
<p>Very simple task but I fail to write the corresponding SQL.</p>

<p>Two factor variables, var1 with 5 levels, var2 is binary with 0/1.</p>

<p>I want to count all rows grouped by var1 and var2, but with new variables.</p>

<pre><code>select
    var1, var2, count(*)
from 
    tab
group by 
    var1, var2;
</code></pre>

<p>gives me</p>

<pre><code>    var1 var2 Count(*)
1   1    0  32
2   1    1  80
3   2    1  80
4   2    0  33
5   3    1  82
6   3    0  33
7   4    1  81
8   4    0  33
9   5    0  33
10  5    1  88
</code></pre>

<p>Desired is this format:</p>

<pre><code>var1 var1=0 var1=1
1    32     80
2    33     80
3    33     82
4    33     81
5    33     88
</code></pre>

<p>Does not work:</p>

<pre><code>select
    var1,
    case 
        when var2 = 1 then count(*)  
    end as svar1,
    case
        when var2 = 0 then count(*)
    end as svar2
from 
    tab
group by 
    var1;
</code></pre>

<p>My query does not work, because var2 is not part of the associated group,
how do I get this format?</p>