SQL aggregate query question
- by Phil
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.