RE: SQL (Count/Duplicate) Question

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

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.