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.

what variety of SQL? MySQL? Microsoft SQL Server? Oracle? Access? etc — UnhandledExcepSean, Mar 16 '12 at 12:11
Does this answer your question? [SQL Query To Obtain Value that Occurs more than once](https://stackoverflow.com/questions/6095567/sql-query-to-obtain-value-that-occurs-more-than-once) — jdhao, Jun 03 '22 at 14:20

Solomon Rutzky · Accepted Answer · 2012-03-16T12:47:11.517

It should be:

SELECT SalesID, COUNT(*)
FROM AXDelNotesNoTracking
GROUP BY SalesID
HAVING COUNT(*) > 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 > 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

This is very helpful. I really appreciate all the varying ways of doing this and explanations. Thanks - Leo — Leo Gurdian, Aug 15 '18 at 21:00
What about the performance issue? will SELECT SalesID, COUNT(*) FROM AXDelNotesNoTracking GROUP BY SalesID HAVING COUNT(*) > 1 be fast? — aidonsnous, Sep 06 '18 at 14:21

score 17 · Answer 2 · answered Mar 16 '12 at 12:17

17

How about:

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

answered Mar 16 '12 at 12:17

Mark J. Bobak

13,720
6
39
67

2

Think this is the best option, since it wont requires unecessary subquerys like in the accepted answer. – Felipe Leão Oct 18 '18 at 16:39

score 1 · Answer 3 · edited Feb 17 '21 at 10:53

1

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

edited Feb 17 '21 at 10:53

Marcello Miorelli

3,368
4
44
67

answered Jul 18 '19 at 17:59

LConrad

816
1
11
20

Alex · Answer 4 · 2012-03-16T12:28:30.370

0

try this:

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

edited Mar 16 '12 at 12:28

answered Mar 16 '12 at 12:14

Alex

5,971
11
42
80

-1 This is missing the HAVING clause to identify only the records that appear more than once, as the original question is requesting. – Solomon Rutzky Mar 16 '12 at 12:23
he didn't mention that he only wants to see the more than 1 records but to check them – Alex Mar 16 '12 at 12:24
2

The question states: "I want to check if a piece of data appears more than once in a particular column". Without the HAVING clause you will get back all values and if the table is large then it will be difficult to find the ones that appear more than once. – Solomon Rutzky Mar 16 '12 at 12:26

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

4 Answers4