sql - simplify a join query? -

i want stars per employee department, table csereduxresponses add stars each department , divide using employee.cse_dept (#of employee in each department). employedept=dept_id, employee=emp_id. made mock tables simplify.

    create table csereduxresponds  (employeedept int, employee int, stars int);  insert csereduxresponds  values(1,1,1); insert csereduxresponds  values(1,1,0); insert csereduxresponds  values(1,1,1); insert csereduxresponds  values(1,2,1); insert csereduxresponds  values(1,2,0); insert csereduxresponds  values(3,3,1); insert csereduxresponds  values(3,3,1); insert csereduxresponds  values(4,3,1); insert csereduxresponds  values(4,3,0); insert csereduxresponds  values(4,3,1); insert csereduxresponds  values(4,3,1); insert csereduxresponds  values(5,4,0); insert csereduxresponds  values(5,4,1); insert csereduxresponds  values(5,4,1);  create table employee ( dept_id int,emp_id int,cse_dept int);  insert employee values (1,1,14); insert employee values (1,2,14); insert employee values (3,3,8); insert employee values (5,4,9); insert employee values (6,5,10);  create table csereduxdepts(csedept_id int, dept_name varchar(25)); insert  csereduxdepts(1,'dapartmen one'); insert  csereduxdepts(2,'dapartmen two'); insert  csereduxdepts(3,'dapartmen 3'); insert  csereduxdepts(4,'dapartmen 4'); insert  csereduxdepts(5,'dapartmen 5'); insert  csereduxdepts(6,'dapartmen 6'); 

i have query similar , gets total_stars of each employee, not department:

  select a.employee, a.execoffice_status,a.employeedept, b.csedept_id department_id , b.csedept_name department_name,   sum(execoffice_status) total_stars,rank() over(order sum(execoffice_status)desc )as rnk   intranet.dbo.csereduxresponses join intranet.dbo.csereduxdepts b   on b.csedept_id = a.employeedept   group employee,execoffice_status,employeedept,csedept_id,csedept_name   order rnk  

i forgo add table has department names. here want ouput department name , stars/cse_dept

based on provided, here's aggregation of stars per employee per department, divided static value of employee's per department (cse_dept). percse shown percentage.

select  employeedept       , employee       , sum(stars) numstars       , max(cse_dept) employeedeptcount       , sum(stars) * 100.00 / max(cse_dept) percse    employee e join    csereduxresponds c         on employeedept = dept_id            , employee = emp_id group employeedept       , employee 

here's join per conditions provided.

select  *    employee e join    csereduxresponds c         on employeedept = dept_id            , employee = emp_id