Ab7NL5F5

1DELIMITER //
2CREATE PROCEDURE contributors_for_department
3(IN con CHAR(20))
4BEGIN
5 
6CREATE TEMPORARY TABLE IF NOT EXISTS temp_department_by_count AS
7SELECT 
8 	DISTINCT(s.Student_Id) AS idea_count,
9 	COUNT(i.Student_Id) AS contributors_count,
10 	d.Dep_Id,d.Name as Dep_name 
11FROM `departments`d 
12LEFTJOIN students s ON s.Dep_Id = d.Dep_id 
13LEFT JOIN ideas i ON i.Student_Id = s.Student_Id 
14GROUP BY s.Student_Id, i.Idea_Id;
15 
16SELECT  
17 	SUM(contributors_count) as count, 
18 	Dep_name 
19FROM temp_department_by_count 
20GROUP BY Dep_Id;
21 
22END //
23DELIMITER //