Apps  Contact  Seminars 

Archive for July 31st, 2009


July 31st, 2009

Venn Diagram Queries

by Amrinder Arora

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.

Tags: ,