Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
SUMPRODUCT to count items with duplicates where another column contains two defined items | Excel Worksheet Functions | |||
Remove Duplicates; How can I get a list of items removed? | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Transfer Items to a list with no duplicates | Excel Worksheet Functions |