count recodrs issue

[RaulMA]

Need help to count records each time it repeats if and only if the three colums are the same.
Cliente Socio Prd other colums (do not care)
client1 M_US Em
client1 M_US Em
client1 M_US Em
client2 M_US Em
client2 M_US Em
client3 A_US HG
client3 X_US HG
client3 X_US NI
client3 X_US NI
______________________________________________
Cliente Socio Prd times
client1 M_US Em 3
client2 M_US Em 2
client3 A_US HG 1
client3 X_US HG 1
client3 X_US NI 2

Thanks
Sereleg

 

[bborissov]

SELECT Cliente, Socio, Prd, COUNT(*) AS times
       FROM MyTable
GROUP BY Cliente, Socio, Prd

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

How bout

select cliente, socio, prd, count(*) from MYTABLE
group by cliente, socio, prd

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[djj55]

Not sure if Sereleg only wants to count the records that are doubled, so may be something like:

SELECT Cliente, Socio, prd, times 
  FROM (SELECT Cliente, Socio, Prd, COUNT(*) AS times
       FROM MyTable GROUP BY Cliente, Socio, Prd) as xx
  WHERE times > 1

-- not tested

djj

 

[bborissov]

Nope,
the result must be:

Cliente    Socio        Prd    times
client1       M_US      Em     3
client2       M_US      Em     2
client3       A_US      HG     [COLOR=red]1[/color]
client3       X_US      HG     [COLOR=red]1[/color]
client3       X_US      NI     2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[RaulMA]

all of them work for me
thanks