BBrBVdMA

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 DISTINCT(s.Student_Id) as idea_count,COUNT(i.Student_Id) as contributors_count,d.Dep_Id,d.Name as Dep_name FROM `departments`d LEFT JOIN students s ON s.Dep_Id = d.Dep_id LEFT JOIN ideas i ON i.Student_Id = s.Student_Id GROUP BY s.Student_Id, i.Idea_Id;
8
9SELECT SUM(contributors_count) as count,Dep_name FROM temp_department_by_count GROUP BY Dep_Id;
10
11END //
12DELIMITER //