Re: Outer Join Hell

From: <michael_bialik_at_my-deja.com>
Date: Thu, 02 Sep 1999 19:13:04 GMT
Message-ID: <7qmi7k$ver$1@nnrp1.deja.com>

Hi.

 I think that your query will count duplicate households.

 May be that one will work properly :
  SELECT state, SUM(hs), SUM(rg), SUM(dm)   FROM ( SELECT DISTINCT state, lastname, city, stret, zipcode,

          1 hs, DECODE(registered,'Y',1,0) rg,
          DECODE(democrat,'Y',1,0) dm
         FROM multi_purpose
         WHERE zipcode = '07010'
        )

  group by state;

 Good luck. Michael.

In article <7qjtok$1is$1_at_nnrp1.deja.com>,   david.archer_at_chase.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 02 1999 - 14:13:04 CDT