I am TRYING to create a query that returns totals based on a household. A
household is defined as people with the same last name, street, city, state
and zip code. I have this defined in a view. Bascially, what I am trying
to do is return the total number of households, the number of registered
households and the number of democratic households. (The registered and
democrat fields are defined in the same view using decode). I thought by
using an outer join on multiple instances of the same table, it would split
up the results correctly, but I am getting the same number for all three
fields. It is returning the number of registered democratic
households....close, but no cigar. Any ideas would be very helpful.
select mp.state,count(distinct(mp2.household)),
count(distinct(mp3.household)),count(distinct(mp4.household)) from
multi_purpose mp, multi_purpose mp2 , multi_purpose mp3, multi_purpose mp4
where mp.member_id=mp2.member_id(+) and mp.member_id=mp3.member_id(+) and
mp.member_id=mp4.member_id(+) and mp.zip_code='07010'
and mp3.registered='Y'
and mp4.democrat='Y'
group by mp.state;
Received on Wed Sep 01 1999 - 10:52:31 CDT
