Hi,

Can anyone help me with a SQL query in Apache Derby SQL to get a "simple" count.

Given a table ABC that looks like this...

    
    id  a   b   c
    1   1   1   1
    2   1   1   2
    3   2   1   3
    4   2   1   1
**  5   2   1   2 **
**  6   2   2   1 **
    7   3   1   2
    8   3   1   3
    9   3   1   1

How can I write a query to get a count of how may distinct values of 'a' have both (b=1 and c=2) AND (b=2 and c=1) to get the correct result of 1. (the two rows marked match the criteria and both have a value of a=2, there is only 1 distinct value of a in this table that match the criteria)

The tricky bit is that (b=1 and c=2) AND (b=2 and c=1) are obviously mutually exclusive when applied to a single row. .. so how do I apply that expression across multiple rows of distinct values for a?

These queries are wrong but to illustrate what I'm trying to do...
"SELECT DISTINCT COUNT(a) WHERE b=1 AND c=2 AND b=2 AND c=1 ..."
.. (0) no go as mutually exclusive
"SELECT DISTINCT COUNT(a) WHERE b=1 AND c=2 OR b=2 AND c=1 ..."
.. (3) gets me the wrong result.
SELECT COUNT(a) (CASE WHEN b=1 AND c=10 THEN 1 END) FROM ABC WHERE b=2 AND c=1
.. (0) no go as mutually exclusive

Cheers, Phil.