Re: Outer Join Hell

From: Michael Ringbo <mhr_at_NOSPAMramboll.dk>
Date: Thu, 02 Sep 1999 09:21:04 +0200
Message-ID: <37CE2560.8FBFE508@NOSPAMramboll.dk>

Hi,

Try using decode. Something like this:

select mp.state,

        count(*) total_number,
        sum(decode(mp.registered,'Y',1,0) tot_registered,
        sum(decode(mp.democrat,'Y',1,0) tot_democrat

from
multi_purpose mp
where mp.zip_code='07010'
group by mp.state;

Hope this helps

Regards, Michael Ringbo

"A. Mills" wrote:

> 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 Thu Sep 02 1999 - 02:21:04 CDT