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

Solution 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

Solution 2:

How about:

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

Solution 3:

To expand on Solomon Rutzky's answer, if you are looking for a piece of data that shows up in a range (i.e. more than once but less than 5x), you can use

having count(*) > 1 and count(*) < 5

And you can use whatever qualifiers you desire in there - they don't have to match, it's all just included in the 'having' statement. https://webcheatsheet.com/sql/interactive_sql_tutorial/sql_having.php