See whether an item appears more than once in a database column

Question

I want to check if a piece of data appears more than once in a particular column in my table using SQL. Here is my SQL code of what I have so far:

select * from AXDelNotesNoTracking where count(salesid) > 1 

salesid is the column I wish to check for, any help would be appreciated, thanks.

Answer 1

It should be:

SELECT SalesID, COUNT(*) FROM AXDelNotesNoTracking GROUP BY SalesID HAVING COUNT(*) > 1 

Regarding your initial query:

1. You cannot do a SELECT * since this operation requires a GROUP BY and columns need to either be in the GROUP BY or in an aggregate function (i.e. COUNT, SUM, MIN, MAX, AVG, etc.)
2. As this is a GROUP BY operation, a HAVING clause will filter it instead of a WHERE

Edit:

And I just thought of this, if you want to see WHICH items are in there more than once (but this depends on which database you are using):

;WITH cte AS (     SELECT  *, ROW_NUMBER() OVER (PARTITION BY SalesID ORDER BY SalesID) AS [Num]     FROM    AXDelNotesNoTracking ) SELECT  * FROM    cte WHERE   cte.Num > 1 

Of course, this just shows the rows that have appeared with the same SalesID but does not show the initial SalesID value that has appeared more than once. Meaning, if a SalesID shows up 3 times, this query will show instances 2 and 3 but not the first instance. Still, it might help depending on why you are looking for multiple SalesID values.

Edit2:

The following query was posted by APC below and is better than the CTE I mention above in that it shows all rows in which a SalesID has appeared more than once. I am including it here for completeness. I merely added an ORDER BY to keep the SalesID values grouped together. The ORDER BY might also help in the CTE above.

SELECT * FROM AXDelNotesNoTracking WHERE SalesID IN     (     SELECT SalesID           FROM AXDelNotesNoTracking           GROUP BY SalesID           HAVING COUNT(*) > 1     ) ORDER BY SalesID 

Answer 2

How about:

select salesid from AXDelNotesNoTracking group by salesid having count(*) > 1;