|
... having count(*) > 1 Having statements are applied after the selection and after grouping. =========================================================== R. Bruce Hoffman, Jr. -- IBM Certified AS/400 Professional System Administrator -- IBM Certified AS/400 Professional Network Administrator "The sum of all human knowledge is a fixed constant. It's the population that keeps growing!" -----Original Message----- From: Bale, Dan <DBale@TFSA.Textron.com> To: 'MIDRANGE-L@midrange.com' <MIDRANGE-L@midrange.com> Date: Monday, February 21, 2000 5:06 PM Subject: HELP: SQL count(*) to weed out duplicates >It's been awhile, but... > >Today a requirement came up where I have to identify duplicate keys in a >file. In the past, I used SQL to do this and seem to recall using count(*) >in some fashion, i.e.: > > select pdiv, ptyp, pvnd, ppo, count(*) from pohedrl3 > group by pdiv, ptyp, pvnd, ppo > >This results in: > > VENDOR > DIV TYPE NUMBER PO NUMBER COUNT ( * ) > --- ---- -------- ---------- -------------- > 1 MSC 70,225 8,101 1 > 1 MSC 110,275 7,724 1 > 1 MSC 160,415 7,725 1 > 1 MSC 160,415 7,726 1 > 1 MSC 160,415 7,727 1 > 1 MSC 160,415 7,728 1 >(continued for 10000 more rows) > >How do I specify that I only want count(*) > 1? Specifying "where count(*) >> 1" causes numerous syntax errors, no matter where I place it. A look at >the infocenter SQL manuals yielded no clues. > >If someone answers this, could you kindly reply to DBale@TFSA.Textron.com as >well as MIDRANGE-L so that I get this ASAP? Thanks! > >- Dan Bale >+--- >| This is the Midrange System Mailing List! >| To submit a new message, send your mail to MIDRANGE-L@midrange.com. >| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. >| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. >| Questions should be directed to the list owner/operator: david@midrange.com >+--- > +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.