RE: SQL (Count/Duplicate) Question -- MIDRANGE-L

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
After reading all the Reponses for both of my SQL questions yesterday, I
just want to say thanks to everyone who helped...  I was given some great
working examples, and a handful of different ways to do them...  Plus I got
to see some new keywords in action...

So thanks again everyone...

Tim



> -----Original Message-----
> From: Vernon Hamberg [SMTP:vhamberg@attbi.com]
> Sent: Thursday, May 09, 2002 8:08 PM
> To:   midrange-l@midrange.com
> Subject:      RE: SQL (Count/Duplicate) Question
>
> put 'as zzz' after the internal select - outside the parentheses. 'zzz'
> can
> be any valid name.
>
> At 05:04 PM 5/9/02 -0700, you wrote:
> >This message is in MIME format. Since your mail reader does not
> understand
> >this format, some or all of this message may not be legible.
> >--
> >[ Picked text/plain from multipart/alternative ]
> >Thanks for the reply.....  but ....
> >
> >When I tried it, I got this error... Any ideas what I did wrong?
> >  ===> select count(*)
> >            from (select fld1,count(*)
> >                    from devtimh/testdup
> >
> >                    group by fld1
> >
> >                    having count(*) > 1)
> >
> >
> >**Error***
> >       Token <END-OF-STATEMENT> was not valid. Valid tokens: AS
> <IDENTIFIER
> >
> >
> >-------
> >
> >
> >Message . . . . :   Token <END-OF-STATEMENT> was not valid. Valid tokens:
> AS
> >   <IDENTIFIER>.
> >Cause . . . . . :   A syntax error was detected at token
> <END-OF-STATEMENT>.
> >   Token <END-OF-STATEMENT> is not a valid token.  A partial list of
> valid
> >   tokens is AS <IDENTIFIER>.  This list assumes that the statement is
> correc
> >   up to the token.  The error may be earlier in the statement, but the
> synta
> >   of the statement appears to be valid up to this point.
> >Recovery  . . . :   Do one or more of the following and try the request
> agai
> >     -- Verify the SQL statement in the area of the token
> <END-OF-STATEMENT>.
> >   Correct the statement.  The error could be a missing comma or
> quotation
> >   mark, it could be a misspelled word, or it could be related to the
> order o
> >   clauses.
> >     -- If the error token is <END-OF-STATEMENT>, correct the SQL
> statement
> >
> >
> > > -----Original Message-----
> > > From: MCPARTLAND, Stan [SMTP:stanley.mcpartland@bently.com]
> > > Sent: Thursday, May 09, 2002 4:50 PM
> > > To:   'midrange-l@midrange.com'
> > > Subject:      RE: SQL (Count/Duplicate) Question
> > >
> > > Tim,
> > >
> > > Give this a try.
> > >
> >         SELECT count(*)
> >           FROM (SELECT field1, count(*)
> >                      FROM tim
> >                      GROUP BY field1
> >                      HAVING count(*) > 1)
> >
> > > Regards,
> > > Stan
> > >
> > >
> > > -----Original Message-----
> > > From: Hatzenbeler, Tim [mailto:thatzenbeler@clinitech.net]
> > > Sent: Thursday, 09 May, 2002 3:18 PM
> > > To: 'midrange-l@midrange.com'
> > > Subject: New: SQL (Count/Duplicate) Question
> > >
> > >
> > > This message is in MIME format. Since your mail reader does not
> understand
> > > this format, some or all of this message may not be legible.
> > > --
> > > [ Picked text/plain from multipart/alternative ]
> > > I have a table, that sometimes has duplicate values...  And I would
> like
> > > to
> > > create an SQL, that will return back to me a count(*) of the number of
> > > duplicates it found, so if my table has this in it...
> > >
> > > Table = Tim
> > > field1
> > > --------
> > > 1
> > > 2
> > > 3
> > > 3
> > > 4
> > > 4
> > > 4
> > > 5
> > >
> > > it would return back a count of  *2*  because there were 2 distinct
> groups
> > > that had duplicates   group (3 and 4).
> > >
> > > Is that easily done?
> > >
> > > Thanks, tim
> > >
> > >
> > > This e-mail message, including any attachments, is for the sole use of
> the
> > > intended recipient(s) and may contain confidential or privileged
> > > information.  Any unauthorized review; use, disclosure or distribution
> is
> > > prohibited.  If you are not the intended recipient, please contact the
> > > sender by reply e-mail and destroy the message.
> > > _______________________________________________
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > > list
> > > To post a message email: MIDRANGE-L@midrange.com
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@midrange.com
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > > _______________________________________________
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > > list
> > > To post a message email: MIDRANGE-L@midrange.com
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@midrange.com
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> >This e-mail message, including any attachments, is for the sole use of
> the
> >intended recipient(s) and may contain confidential or privileged
> >information.  Any unauthorized review; use, disclosure or distribution is
> >prohibited.  If you are not the intended recipient, please contact the
> >sender by reply e-mail and destroy the message.
> >_______________________________________________
> >This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> >To post a message email: MIDRANGE-L@midrange.com
> >To subscribe, unsubscribe, or change list options,
> >visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> >or email: MIDRANGE-L-request@midrange.com
> >Before posting, please take a moment to review the archives
> >at http://archive.midrange.com/midrange-l.
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
This e-mail message, including any attachments, is for the sole use of the
intended recipient(s) and may contain confidential or privileged
information.  Any unauthorized review; use, disclosure or distribution is
prohibited.  If you are not the intended recipient, please contact the
sender by reply e-mail and destroy the message.