Re: SQL help. Counting duplicate rows (Grrrr kill users!)

Ian Wilkins wrote:
>
> I know someone out there can help me with this.
>
> My users have handily duplicated _some_ of the data in a table.
> I would like to know if all the data is duplicated, or some, or some might
> be duplicated more than once etc etc.
>
> So how do I list out, for all the different values in col1, how many times
> the same value appears in col2. I.e.:
>
> What I want to get from:
> table1
> col1 col2
> 123 xxx
> 123 xxx
> 123 xxx
> 123 zzz
> 123 zzz
> 123 zzz
> 456 xxx
> 456 zzz
>
> is result
>
> 123 3
> 456 1
>
> I *think* I can assume that all rows for each distinct value of col1 have
> been duplicated the same number of times.
>
> Ta muchly in advance. Esp. if you email me on ianw_at_teleord.co.uk
>
> Ian.
> *----------------------------------------------*
> * "Think for yourself - Question Authority" *
> * Dr. Timothy Leary (1920 - 1996) *
> *----------------------------------------------*

How about using the following:

select col1,col2, count(*)
  from table1
 group by col1, col2;

This would give the following results:

123	xxx	3
123	zzz	3
456	xxx	1
456	zzz	1

Is this the results you were looking for?

Theresa Stacy
The Boeing Company
Boeing Defense & Space Group Received on Tue Nov 26 1996 - 00:00:00 CST