Sql: Select Rows with a Column Value That Occurs at Least N Times

SQL: Select rows with a column value that occurs at least N times?

SELECT fname, lname FROM Celebrities 
WHERE lname IN 
 (SELECT lname FROM Celebrities 
  GROUP BY lname HAVING COUNT (lname) >1)

Only return rows where the value appears more than n times

Use the Group By clause with Having:

SELECT id 
FROM dbo.TableName
GROUP BY ID
HAVING COUNT(*) >= 3

Demo

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 - Select row if appear more than x times in other table

I'm assuming (and hoping) that you're not storing the user's name twice, since that leads to data quality issues when the user changes their name.

Assuming the tables are structured like below:

CREATE TABLE
  Members
(
    UserID INT,
    Name VARCHAR(15)
);

INSERT INTO
  Members
VALUES
(111, 'Peter'),
(222, 'Bart'),
(333, 'Joe'),
(444, 'Andrew');

CREATE TABLE
  Orders
(
   OrderID INT,
   UserID INT
);

INSERT INTO
  Orders
VALUES
(777, 111),
(888, 333),
(999, 111),
(101, 444),
(102, 111),
(103, 333);

You can use a GROUP BY and HAVING clause which would give you the UserID of all users with more than 1 (or whichever number you choose) orders. Then, you join that to the Members table to get the name.

SELECT
  Orders.UserID,
  Members.Name
FROM
  Orders
INNER JOIN
  Members
  ON Orders.UserID = Members.UserID
GROUP BY
  UserID,
  Members.Name
HAVING
  COUNT(OrderID) > 1;

SQLFiddle: http://sqlfiddle.com/#!9/1dadc4/2

However, if you already have the names stored (and that's not changing), then you could skip the JOIN like below:

SELECT
  UserID,
  Name
FROM
  Orders
GROUP BY
  UserID,
  Name
HAVING
  COUNT(OrderID) > 1

select values of a column which appear N times in sql

I'm not entirely sure of what you're asking for but does this help?

select e.emp_no, e.first_name, e.last_name
from   employees e, dept_emp de
where  e.emp_no=de.emp_no
group by e.emp_no, e.first_name, e.last_name
having count(*) = (select count(*) from departments);

SQL query to count how many values appear N times in a column

select count(team) from 
(select  team, COUNT(team) as countTeam from t group by team) 
where countTeam = 2

Replace 2 by the number you want.

Demo

Show all rows where value appears at least once in a group

Test setup (note that Group is a reserved word)

create table t(rn NUMERIC(2), id varchar (3), thegroup char)

insert t values(1,'001','A')
insert t values(2,'001','B')
insert t values(3,'001','B')
insert t values(1,'002','B')
insert t values(2,'002','B')

Needed query

select *
from t
where id in (
  select id
  from t
  where thegroup in ('A')
)

Result

rn |id  |thegroup |
---|----|---------|
1  |001 |A        |
2  |001 |B        |
3  |001 |B        |

Query that checks for a value A to appear at least B number of times in C columns

You could use try summing CASE expressions which check for the letter h in each of the 3 greetings:

SELECT *
FROM GreetingsAndFarewell
WHERE CASE WHEN greetings1 LIKE '%h%' THEN 1 ELSE 0 END +
      CASE WHEN greetings2 LIKE '%h%' THEN 1 ELSE 0 END +
      CASE WHEN greetings3 LIKE '%h%' THEN 1 ELSE 0 END >= 2;

On BigQuery you might be able to directly sum boolean expressions and simplify to:

SELECT *
FROM GreetingsAndFarewell
WHERE greetings1 LIKE '%h%' + greetings2 LIKE '%h%' + greetings3 LIKE '%h%' >= 2;

Find most frequent value in SQL column

SELECT
  <column_name>,
  COUNT(<column_name>) AS `value_occurrence` 

FROM
  <my_table>

GROUP BY 
  <column_name>

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

Replace <column_name> and <my_table>. Increase 1 if you want to see the N most common values of the column.

SQL Query Help: Selecting Rows That Appear A Certain Number Of Times

select dates 
  from table t 
 group by dates having count(dates) < k ;

Hopefully, it works for ORACLE.
HTH

---

Related Topics