SQL - list items that have duplicates
 

I can't seem to figure out how to list items that have duplicates. I can
list items and their related counts, but can't just list the items with
duplicates.

This SQL statement lists items and their counts
SELECT VENDID, COUNT(*) AS VENDIDCOUNT FROM VENDTABLE GROUP BY VENDID

but this one doesn't seem to work
SELECT VENDID FROM VENDTABLE WHERE VENDIDCOUNT 1 IN (SELECT COUNT(*) AS
VENDIDCOUNT FROM VENDTABLE GROUP BY VENDID)

Thanks for the help!
Dave

SQL - list items that have duplicates
 

you need a HAVING statement eg
here's an example

SELECT VENDID, COUNT(*) AS VENDIDCOUNT FROM
(
select * from

(select 'A'[VENDID]
union all
select 'B'
union all
select 'C'
union all
select 'B'
) [VENDTABLE]
)[x]
group by VENDID
having count(vendid)1

Patrick Molloy
Microsoft Excel MVP

"dave k" wrote:

I can't seem to figure out how to list items that have duplicates. I can
list items and their related counts, but can't just list the items with
duplicates.

This SQL statement lists items and their counts
SELECT VENDID, COUNT(*) AS VENDIDCOUNT FROM VENDTABLE GROUP BY VENDID

but this one doesn't seem to work
SELECT VENDID FROM VENDTABLE WHERE VENDIDCOUNT 1 IN (SELECT COUNT(*) AS
VENDIDCOUNT FROM VENDTABLE GROUP BY VENDID)

Thanks for the help!
Dave