SQL - list items that have duplicates

[Guest]

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

 

[Guest]

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

 

[Guest]

It works!
Thanks

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

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

 

[onedaywhen]

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)

FWIW, I think the query you were trying to write is:

SELECT T1.VENDID,
COUNT(*) AS VENDIDCOUNT
FROM VENDTABLE AS T1
WHERE 1 < (
SELECT COUNT(*)
FROM VENDTABLE
WHERE VENDID = T1.VENDID
) GROUP BY T1.VENDID;

Jamie.

--