CODEWITHSUNDEEP
sql-server
Yael
Yael

Reputation: 45

select if there are more than n results but with conditions

Unfortunately, similar question didn't include few "conditions" for this case.

So, my Table is:

| TimeId | Work_Role | User_Name |
----------------------------------
|    1   | users     | Oran      | *
|    2   | admin     | Ray       |
|    3   | users     | Oran      | *
|    4   | servs     | Amit      |
|    5   | admin     | Oran      |
|    6   | users     | Ray       |
|    7   | users     | Oran      | *
|    8   | servs     | Amit      |
|    9   | admin     | Oran      |
|   10   | users     | Oran      | *

I wish to display a list for user_name = "Oran" and Work_Role = "users" ONLY if there are more than 2 in the table.

In this case I expect to see: 

| TimeId | Work_Role | User_Name |
----------------------------------
|    1   | users     | Oran      | 
|    3   | users     | Oran      | 
|    7   | users     | Oran      | 
|   10   | users     | Oran      |

I was trying to use HAVING according similar questions here, but it didn't provide any results. I assumed that maybe the multiple conditions have something to do with it.

So this is the "main" code:

SELECT *
FROM Table
WHERE Work_Role = 'users' AND User_Name = 'Oran'

How do I add the condition to select only if there are more than 2 results?...

Upvotes: 0

Views: 90

Answers (3)

rabin
rabin

Reputation: 270

just use this query its should work 

SELECT UserName,Work_Role
   FROM yourtableName
   WHERE UserName='oran' AND Work_Role='users'
   GROUP BY UserName,Work_Role
   Having Count(Work_Role)>2

Upvotes: 1

Uncle Iroh
Uncle Iroh

Reputation: 6055

SELECT * 
FROM   users u JOIN 
        (  SELECT user_name 
           FROM   users 
           GROUP BY user_name 
           HAVING count(user_name) > 2
        ) AS u2 ON u.user_name = u2.user_name

--

example

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

SAMPLE DATA

CREATE TABLE yourTable(
    TimeId      INT,
    Work_Role   VARCHAR(10),
    User_Name   VARCHAR(10)
)
INSERT INTO yourTable VALUES
(1, 'users', 'Oran'),
(2, 'admin', 'Ray'),
(3, 'users', 'Oran'),
(4, 'servs', 'Amit'),
(5, 'admin', 'Oran'),
(6, 'users', 'Ray'),
(7, 'users', 'Oran'),
(8, 'servs', 'Amit'),
(9, 'admin', 'Oran'),
(10, 'users', 'Oran');

SOLUTION #1: USING CTE

WITH cte AS(
    SELECT *, cc = COUNT(*) OVER(PARTITION BY Work_Role, User_Name) FROM yourTable
)
SELECT
    TimeId,
    Work_Role,
    User_Name
FROM cte
WHERE
    Work_Role = 'users' 
    AND User_Name = 'Oran'
    AND cc > 2

SOLUTION #2: USING JOIN

SELECT
    t.*
FROM yourTable t
INNER JOIN(
    SELECT
        Work_Role,
        User_Name,
        cc = COUNT(*)
    FROM yourTable
    GROUP BY Work_Role, User_Name
)g
    ON t.Work_Role = g.Work_Role
    AND t.User_Name = g.User_Name
WHERE
    t.Work_Role = 'users' 
    AND t.User_Name = 'Oran'
    AND cc > 2

CLEANUP SAMPLE DATA

DROP TABLE yourTable

Upvotes: 0

Related Questions