Group by in SQLite over one subset of a set based on the elements of another subset of the set...

Question

I have a dataset something like this

A       |  B      | C         | D        | E
-----------------------------------------------
1       |  Carrot | <null>    | a        | 111
2       |  Carrot | <null>    | b        | 222
3       |  Carrot | zzz       | c        | 333
4       |  Banana | <null>    | a        | 444
5       |  Banana | <null>    | b        | 555
6       |  Banana | <null>    | c        | 666
7       |  Grape  | <null>    | a        | 777
8       |  Grape  | <null>    | b        | 888
9       |  Grape  | zzz       | c        | 999
10      |  Carrot | <null>    | a        | 000
11      |  Carrot | <null>    | b        | AAA
12      |  Carrot | zzz       | c        | BBB

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.

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.

My code looks something like this

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

Or something like that (there are joins across three tables going on too...)

Is that at all comprehensible?

Answer 1

Unless I'm missing something, this should be what you're looking for:

  SELECT t.b_column,
         SUM(t.e_column)
    FROM TABLE t
   WHERE t.d_column = 'b'
     AND t.c_column = 'zzz'
GROUP BY t.b_column