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.

User · Answer

How about   select salesid from AXDelNotesNoTracking group by salesid having count     gt  1

User · Answer

It should be   SELECT SalesID  COUNT    FROM AXDelNotesNoTracking GROUP BY SalesID HAVING COUNT     gt  1   Regarding your initial query    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   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  gt  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     gt  1       ORDER BY SalesID

User · Answer

try this   select salesid count  salesid  from AXDelNotesNoTracking group by salesid having count  salesid   gt 1

User · Answer

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     gt  1 and count     lt  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

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

The answer is

Examples related to sql

Tags