Daily Archives: July 31, 2009

Venn Diagram Queries

Venn Diagram queries in Oracle have saved the day for me many many times. Consider this:

You have a table “analyzethis”, and say mv (modelvalue) is in 0/1 and tv (TruthValue) is in 0/1. Then, the following query does something nice.

select sum (val), match from
(select count(*) val, ‘M’ as match from analyzethis where mv=1 and tv=1 union
select count(*), ‘M’ from analyzethis where mv=0 and tv=0 union
select count(*), ‘N’ from analyzethis where mv=1 and tv=0 union
select count(*), ‘N’ from analyzethis where mv=0 and tv=1)
group by match

This query returns:

Sum(val), Match
1450, M
2300,N

Of course a similar result can also be obtained by decode, sign and square functions.