CODEWITHSUNDEEP
mysqlsql
codeepic
codeepic

Reputation: 4102

SQL - how can I get a number of admins in the same team

I have a team_member table with simplified structure like this

team_member_id | team_id | role
1              | 1       | ADMIN
2              | 1       | USER 
3              | 2       | ADMIN
4              | 2       | ADMIN

So the last two guys are admins on the same team. I need to build SQL query that will tell me how many teams there are with more than one admin per team.

Upvotes: 0

Views: 98

Answers (3)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT COUNT(team_id) -- Count Of Team_id Having More Than One ADMIN
FROM (
SELECT team_id,COUNT(*) -- COUNT ADMIN PER Team_id
FROM Table1
WHERE role='ADMIN'
GROUP BY team_id
HAVING COUNT(*)>1
) AS T1

Live Demo

http://sqlfiddle.com/#!9/978f4/15

Upvotes: 2

Sinto
Sinto

Reputation: 3997

SELECT COUNT(`role`) 
FROM TABLE_NAME WHERE `role` = 'Admin' 
GROUP BY `team_id`
HAVING COUNT(`role`) > 1

Upvotes: 1

shawkins1
shawkins1

Reputation: 113

SELECT COUNT(Role),
        Team_id

FROM MyTable

WHERE Role = 'Admin'

GROUP BY Team_id

HAVING COUNT(Role) > 1

Upvotes: 1

Related Questions