Re: AW: AW: how to use count in sql

[mlazarus <mlazarus@xxxxxxxx>]

Shouldn't that be:

Having Count(*)>  1) instead of Having Count(*)>  0) ?

 -mark

On 6/5/2015 6:23 PM, Birgitta Hauser wrote:
> > >   I now know that I have several duplicates.  Is there any way to get a
> > >        
> list of just duplicate records, and their relative record number?
>
> Try:
>
>
>
> With x as (Select ORORD#, OTUSM, OTTRNC
>
>                From rklib/clspaytpa
>
>                Group By ORORD#, OTUSM, OTTRNC
>
>                Having Count(*)>  0)
>
> Select rrn(a), a.*
>
> > From x join rklib/clspaytpa a on     x.ORORD# = a.ORORD#
>
>                                   and x.OTUSM  = a.OTUSM
>
>                                   and x.OTTRNC = a.OTTRNC
>
>
>
> Mit freundlichen Grüßen / Best regards
>
>
>
> Birgitta Hauser
>
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
>
> "If you think education is expensive, try ignorance." (Derek Bok)
>
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
> paul.roy@xxxxxxx
> Gesendet: Friday, 05.6 2015 14:32
> An: Midrange Systems Technical Discussion
> Betreff: Re: AW: how to use count in sql
>
>
>
> That kind of instruction should do it (not tested)
>
>
>
> Select RRN(clspaytpa),ORORD#, OTUSM, OTTRNC from clspaytpa where
> concat(orord#, concat( otusm, ottrnc))  in
>
>
>
> (
>
> Select concat(orord#, concat(otusm, ottrnc))
>
>         From rklib/clspaytpa
>
>         Group By ORORD#, OTUSM, OTTRNC
>
>         Having Count(*)>  0
>
> )
>
>
>
> Paul
>
>
>
>
>
>
>
>
>
> From:<mailto:rob@xxxxxxxxx>  rob@xxxxxxxxx
>
> To:     Midrange Systems Technical Discussion<
> <mailto:midrange-l@xxxxxxxxxxxx>  midrange-l@xxxxxxxxxxxx>
>
> Date:   05/06/2015 13:41
>
> Subject:        Re: AW: how to use count in sql
>
> Sent by:        "MIDRANGE-L"<  <mailto:midrange-l-bounces@xxxxxxxxxxxx>
> midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
>
>
>
>
> Birgitta's right.  And I was going to suggest a link at lmgtfy...
>
>
>
> Ok, does anyone know that next question we should expect?
>
>
>
> No?
>
>
>
> Ok, here it is:  I now know that I have several duplicates.  Is there any
> way to get a list of just duplicate records, and their relative record
> number?
>
>
>
>
>
> Rob Berendt
>
> --
>
> IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
> to:  2505 Dekko Drive
>
>            Garrett, IN 46738
>
> Ship to:  Dock 108
>
>           6928N 400E
>
>            Kendallville, IN 46755
>
>   <http://www.dekko.com>  http://www.dekko.com
>
>
>
>
>
>
>
>
>
>
>
> From:   "Birgitta Hauser"<  <mailto:Hauser@xxxxxxxxxxxxxxx>
> Hauser@xxxxxxxxxxxxxxx>
>
> To:     "'Midrange Systems Technical Discussion'"
>
> <  <mailto:midrange-l@xxxxxxxxxxxx>  midrange-l@xxxxxxxxxxxx>
>
> Date:   06/04/2015 09:58 PM
>
> Subject:        AW: how to use count in sql
>
> Sent by:        "MIDRANGE-L"<  <mailto:midrange-l-bounces@xxxxxxxxxxxx>
> midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
>
>
>
>
> No need for a CTE! The following statement will do the job.
>
>
>
> Select ORORD#, OTUSM, OTTRNC, Count(*)
>
>         From rklib/clspaytpa
>
>         Group By ORORD#, OTUSM, OTTRNC
>
>         Having Count(*)>  0
>
>
>
> Mit freundlichen Grüßen / Best regards
>
>
>
> Birgitta Hauser
>
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
>
> Brown)
>
> "If you think education is expensive, try ignorance." (Derek Bok)
>
> "What is worse than training your staff and losing them? Not training them
>
> and keeping them!"
>
>
>
> -----Ursprüngliche Nachricht-----
>
> Von: MIDRANGE-L [<mailto:midrange-l-bounces@xxxxxxxxxxxx>
> mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
>
> Alan
>
> Campin
>
> Gesendet: Thursday, 04.6 2015 23:49
>
> An: Midrange Systems Technical Discussion
>
> Betreff: Re: how to use count in sql
>
>
>
> Use a CTE.
>
>
>
> With List As( Select ORORD#, OTUSM, OTTRNC, Count(*) as MyCount
>
>                                    From rklib/clspaytpa
>
>                                    Group By ORORD#, OTUSM, OTTRNC) Select *
>
> from List
>
>    Where MyCount>  1
>
>
>
> I think that is right.
>
>
>
>
>
>
>
> On Thu, Jun 4, 2015 at 2:37 PM,<  <mailto:paul.roy@xxxxxxx>
> paul.roy@xxxxxxx>  wrote:
>
>
>
>    
> >      
>    
> > I would use   Group by
> >      
>    
> >      
>    
> >      
>    
> >      
>    
> > Paul
> >      
>    
> >      
>    
> >      
>    
> >      
>    
> > > On 4 juin 2015, at 23:31, "Hoteltravelfundotcom"<
> > >        
>    
> >   <mailto:hoteltravelfun@xxxxxxxxx>  hoteltravelfun@xxxxxxxxx>
> >      
>    
> > wrote:
> >      
>    
> > >        
>    
> > > I would like to see a list of duplicate rows according to these 3
> > >        
> fields.
>
>    
> > > but this is running
> > >        
>    
> > > how to show me where there is duplicate. there are 100's of
> > >        
>    
> > > thousands of rows so i cannot go thru on by one.
> > >        
>    
> > >        
>    
> > > SELECT otord#, otusrn, ottrnc, count(*) FROM rklib.clspaytpa WHERE
> > >        
>    
> > > ottrnd>  20150500 ORDER BY otord#, otusrn, ottrnc
> > >        
>    
> > >        
>    
> > > HAVING count(*)>  1
> > >