CODEWITHSUNDEEP
mysqlsqlsubquerycounting
root
root

Reputation: 1583

counting the result of a query

Person table
---------------------
|email (pk)   | name|
---------------------
|[email protected]| A   |
|[email protected]| B   |
|[email protected]| C   |

 Role table
---------------------------------
|Role               |Power     |
-------------------------------|
|Primary            |20        |
|Secondary          |10        |
|Supervisor         |30        |
--------------------------------
Assignment table
------------------------------------------------------------------
|Team Name| Term    | Role      |Email         |Join_date
------------------------------------------------------------------
|AA       |2013_1   |Supervisor |[email protected] |2013-08-05
|BB       |2013_1   |Secondary  |[email protected] |2013-08-05
|CC       |2013_1   |Supervisor |[email protected] |2013-08-05
|DD       |2013_1   |Secondary  |[email protected] |2013-08-05
|AA       |2013_1   |Secondary  |[email protected] |2013-08-05

My expected result 

|name | email        | num_of_time_pri | num_of_time_sec | num_of_time_sup|
---------------------------------------------------------------------------------------
|A    | [email protected]|0                |2                | 1              |
|B    | [email protected]|0                |1                | 0              |
|C    | [email protected]|0                |0                | 1              |

Given a term, eg 2013_1 I have to find all the person who is assigned to least one role. And count how many of each role the person is assigned to.

Using this query:

select 
      distinct p.name,
      p.email 
 from assignment a,person p 
 where term ='2013_1' and 
       a.email = p.email;

assume it returns 3 rows as seen in person table. And from there, I want to get the expected result table. How do I continue from there?

Upvotes: 0

Views: 34

Answers (1)

Rigel1121
Rigel1121

Reputation: 2026

Use an aggregate case statement. And, as much as possible refrain from using the implicit join.

select 
      p.name,
      p.email,
      sum(case when role='Primary' then 1 else 0 end) as num_of_time_pri,
      sum(case when role='Secondary' then 1 else 0 end) as num_of_time_sec,
      sum(case when role='Supervisor' then 1 else 0 end) as num_of_time_sup
from assignment a
inner join person p on a.email = p.email
where term ='2013_1'
group by p.name,p.email;

OR

select 
      p.name,
      p.email,
      count(case when role='Primary' then 1 end) as num_of_time_pri,
      count(case when role='Secondary' then 1 end) as num_of_time_sec,
      count(case when role='Supervisor' then 1 end) as num_of_time_sup
from assignment a
inner join person p on a.email = p.email
where term ='2013_1'
group by p.name,p.email;

Upvotes: 1

Related Questions