Re: Outer Join Hell

Have you tried a decode? A coworker put me onto this little gem which has worked wonders for me. I only wish I could claim credit. I have taken the liberty of rewriting your query.

SELECT state,
 COUNT(*),
 SUM(DECODE(registered,'Y',1,0)),
 SUM(DECODE(democrat,'Y',1,0))
FROM multi_purpose
WHERE zip_code = '07010'
GROUP BY state;

Notice the you are summing the result of the DECODE. I think this will work as expected.

Happy Coding,

David Archer

In article <7qji36$hkq$1_at_autumn.news.rcn.net>,   "A. Mills" <cici_at_erols.com> 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;
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 01 1999 - 14:11:28 CDT