Skip to content
Advertisement

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

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.

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement