COUNT & GROUP BY

[stoggers]

Hi,

I have a table similar to:

REF NAME
1 Mike
2 John
3 John
4 Dave

...and I would like to count the number of duplicates using SQL similar to:

SELECT * FROM
(SELECT NAME,COUNT(*)AS C FROM TABLEA GROUP BY NAME) AS TEMP
WHERE C > 1;

The problem I now have is that I would like to link the REF column to other related tables but the count query does not return the REF column. If I include it in the SELECT I get the usual column in GROUP not in SELECT complaint.

Any suggestions?

Mike.

 

[PruSQLer]

You can accomplish this easily with a Table Expression:

With Temp
As (
Select name
From Table_A
Group by name
Having count(*) > 1 )

Select a,b,c, etc
From temp
, Table_A
, Table_B
Where temp.name = Table_A.name
and Table_A.ref = Table_B.ref
etc, etc

 

[2742]

Hi There,

You might find the having clause helpful.

eg select ref_id, ref_des name from tablea, tableb where
tablea.ref_id = tableb.ref_id
and name in (select name from tablea
group by name
having count(*) > 1);

I hope this is clear but basically you select all the info from your other tables you want to know about(only the duplicate rows and join them together and then to get the id of the duplicates you have and in clause that uses the having clause.

I hope this helps.