CODEWITHSUNDEEP
mysqlsql
Satish Saini
Satish Saini

Reputation: 2968

Get users count on the basis of a another column

I am stuck in a problem explained below:

id | user_id | admin
1  |    1    |   2
2  |    1    |   5
3  |    2    |   5
4  |    2    |   5
5  |    3    |   5
6  |    4    |   5

I need to write a query to get all the users who are associated with an admin id passed in WHERE clause AND has more than one transactions(can be with another admin).

If user_id is 5 then result should come

user_id
1
2

Have tried

SELECT 
 user_id ,
 COUNT(*) count
FROM 
 table
 WHERE admin = 5
GROUP BY
 user_id
Having
count > 1

but this above-mentioned query skips the user_id that has only one transaction with admin = 5 and has another row with another admin.

Suggestions?

Upvotes: 0

Views: 101

Answers (5)

Lindsay Moss
Lindsay Moss

Reputation: 21

If I understand your question, you want to ask:

  1. Show me all users who have more than one admin associated
  2. Also allow me to identify which admin specifically is in question (in your example 5)

This would give you a list of every user (user id only)

SELECT  DISTINCT table.user_id
FROM    table
        JOIN (
            SELECT  user_id 
                    ,COUNT(DISTINCT admin_id) num_admins
            FROM    table
            GROUP BY user_id
            HAVING  COUNT(DISTINCT admin_id) > 1
            ) multi_admin
            ON table.user_id = multi_admin.user_id
WHERE   admin = 5 -- if you want to look only at user_ids somehow associated with

Upvotes: 0

venkatesh
venkatesh

Reputation: 151

SELECT user_id FROM test_table1 GROUP BY user_id HAVING SUM(CASE WHEN admin = &n THEN 1 ELSE 0 END ) > 0 AND COUNT(user_id) > 1;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

Aggregate on the user_id and assert that:

  • The admin of interest (e.g. 5) appears,
  • Two or more admins of any type appear


SELECT user_id
FROM yourTable
GROUP BY user_id
HAVING SUM(CASE WHEN admin = 5 THEN 1 ELSE 0 END) > 0 AND
       COUNT(*) > 1;

Demo

Upvotes: 2

Shadow
Shadow

Reputation: 34294

In your query filter on admin=5 and with a separate subquery on those users, who have more than 1 transactions. You need to have the count in a subquery because the admin=5 criterion does not apply to the count.

SELECT DISTINCT user_id
FROM yourTable
WHERE admin_id=5
    AND user_id IN (SELECT user_id FROM yourTable GROUP BY user_id HAVING COUNT(*)>1)

Upvotes: 0

Gaurav Kandpal
Gaurav Kandpal

Reputation: 1330

Try this 

SELECT user_id from `tablename` WHERE admin IN( SELECT admin FROM `tablename` GROUP BY admin HAVING count(*) > 1)

Upvotes: 0

Related Questions