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 ]
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.