SQL Query to Obtain Value That Occurs More Than Once

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

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

SQL Query To Obtain Value that Occurs more than once

For SQL Server 2005+

;WITH T AS
(
SELECT *, 
       COUNT(*) OVER (PARTITION BY Lastname) as Cnt
FROM Students
)
SELECT * /*TODO: Add column list. Don't use "*"                   */
FROM T
WHERE Cnt >= 3

MySQL Query To For Value that Occurs more than once for the same clientId

You can do it by GROUP BY and HAVING.

SELECT clientID, chargeName
FROM charges
WHERE chargeName LIKE 'late fee'
GROUP BY clientID HAVING count(clientID) > 1

select rows where criteria appears more than once sql

select o.*
from v_tem o join 
(select id,temID from v_tem group by id,temID having count(1)>1) t
on o.id=t.id and o.temID=t.temID

t table gives you all the id-temID group that occurs more than once.

So join the original table o with t gives you all the records you need.

sql fiddle here [http://sqlfiddle.com/#!9/b69790/1]

PostgreSQL Query To Obtain Value that Occurs more than once in 12 months

You would use window functions. The simplest method is lag():

select count(distinct customer)
from (select s.*,
             lag(date) over (partition by customer order by date) as prev_date
      from sales s
     ) s
where prev_date > s.date - interval '12 month';

Count records which occurs more than once in a column

This will give you required 3:

SELECT COUNT(*) FROM
(SELECT values 
 FROM table 
 GROUP BY values 
 HAVING COUNT(*) > 1) t

Select Rows that appear more than once

You want to use count(*) as a window function, rather than row_number():

select t.PersonId, t.PersonScore
from (select t.*, count(*) over (partition by PersonId) as cnt
      from TableOne t
     ) t
where cnt > 1;

How to select all rows where a multiple column value occurs more than once in postgresql?

Use window function - count as follows

select * from
(select t.*,
       count(1) over (partition by product_id, entry_Date) as cnt
  from t) t
where cnt > 1

You can also use exists as follows:

select * from t
where exists
(select 1 from t tt
where t.product_id = tt.product_id
  and t.entry_Date = tt.entry_date
  and t.id <> tt.id)

Sql query to check if a certain value appears more than once in rows

Declare @UserID as bigint    
Set @UserID = 2

select Distinct Count(CompanyID)
FROM ComapynUser
Where UserId = @UserId

I think this gives you what you need.

---

Related Topics