Counting number of occurrence

Question

Person table
---------------------
|email (pk)   | name|
---------------------
|a@hotmail.com| A   |
|b@hotmail.com| B   |
|c@hotmail.com| C   |

 Role table
---------------------------------
|Role               |Power     |
-------------------------------|
|Primary            |20        |
|Secondary          |10        |
|Supervisor         |30        |
--------------------------------
Assignment table
------------------------------------------------------------------
|Team Name| Term    | Role              |Email         |Join_date
------------------------------------------------------------------
|AA       |2013_1   |Supervisor |a@hotmail.com |2013-08-05
|BB       |2013_1   |Secondary  |a@hotmail.com |2013-08-05
|CC       |2013_1   |Supervisor |c@hotmail.com |2013-08-05
|DD       |2013_1   |Secondary  |a@hotmail.com |2013-08-05
|AA       |2013_1   |Secondary  |b@hotmail.com |2013-08-05

My expected result

|name | email        | num_of_time_pri | num_of_time_sec | num_of_time_sup|
---------------------------------------------------------------------------------------
|A    | a@hotmail.com|0                |2                | 1              |
|B    | b@hotmail.com|0                |1                | 0              |
|C    | c@hotmail.com|0                |0                | 1              |

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?

Why are the role names different in the `Role` and `Assignment` tables? How are they supposed to be related? — Barmar, Mar 27 '15 at 08:50

score 0 · Answer 1 · answered Mar 27 '15 at 08:51

What you should really do is group and count the data in the RDBMS:

SELECT   p.name, email, Role, COUNT(a.email)
FROM     (Person p CROSS JOIN Role r) LEFT JOIN Assignment a USING (email, Role)
GROUP BY email, Role

And then pivot it, if/as required, in your application code.

Counting number of occurrence

1 Answers1