title: Group by in SQLite over one subset of a set based on the elements of another subset of the set
body: <p>I have a dataset something like this</p>
  
  <pre><code>A       |  B      | C         | D        | E
  -----------------------------------------------
  1       |  Carrot | &lt;null&gt;    | a        | 111
  2       |  Carrot | &lt;null&gt;    | b        | 222
  3       |  Carrot | zzz       | c        | 333
  4       |  Banana | &lt;null&gt;    | a        | 444
  5       |  Banana | &lt;null&gt;    | b        | 555
  6       |  Banana | &lt;null&gt;    | c        | 666
  7       |  Grape  | &lt;null&gt;    | a        | 777
  8       |  Grape  | &lt;null&gt;    | b        | 888
  9       |  Grape  | zzz       | c        | 999
  10      |  Carrot | &lt;null&gt;    | a        | 000
  11      |  Carrot | &lt;null&gt;    | b        | AAA
  12      |  Carrot | zzz       | c        | BBB
  </code></pre>
  
  <p>Now what I'm trying to do is sum up the values of E where D has a value of b, but only for those elements in B where C has a value of zzz.  So in the above example above I am looking at the values 222, 888, and AAA, but I'm not interested in 555.  </p>
  
  <p>I can sum these up by group and sum based on value B no problem, but what I would like to do is sum based on C.  </p>
  
  <p>My code looks something like this</p>
  
  <pre><code>select B,
         (select sum(E) as duty_payable
            from table table_alias_b
           where D = "b"
             and table_alias_b.B = table_alias_a.B )
    from table table_alias_a
   where table_alias_b.B in (select B
                              from table
                             where (C = 'zzz'))
  group by B
  </code></pre>
  
  <p>Or something like that (there are joins across three tables going on too...)</p>
  
  <p>Is that at all comprehensible?</p>