Working sql: it is required that the average number of awards cannot be displayed by ranking

Work SQL:

demand :

It is required that the average number of awards cannot be displayed by ranking

--  The number of teachers in this department who won the prize in teaching and research   Change it to ： Department ranking  ：5/145 
		select 
		RANK() OVER(partition by  a.xymc ORDER BY JYHJCS desc) || '/' || b.num JSRS,
		a.jzgh,
		a.JYHJCS,
		a.xymc
		from 
		(
			select 
			a.jzgh,
			a.xymc,
			sum(a.num)  JYHJCS
			from 
			(
			-- huojiang list 
			select 
			count(distinct a.CGMC) num,
			b.jzgh,
			b.xymc
			from 
			DC.jsx b
			join 
			MODEL.hjmd a 
			on a.ZYWCR=b.jzgxm
			where b.GWLX=' Full-time teacher '
			group by b.jzgh,b.xymc			
			union all			
			-- Achievement Award 
			select   
			count(distinct a.CGMC) num,
			b.jzgh,
			b.xymc
			from 
			DC.JSXXB b
			join 
			MODEL.JXCGJ a 
			on a.HJZ=b.jzgxm
			where b.GWLX=' Full-time teacher '
			group by b.jzgh,b.xymc
			) a 
			group by a.jzgh,a.xymc
		) a

analysis

The original can be found by observation SQL / On the right is the total number of people in this department , Just add up all the winners in this department .

The modified SQL

select SUM(JYHJCS)  nums,xymc from (
   select 
   a.jzgh,
   a.xymc,
   sum(a.num)  JYHJCS
   from 
   (
   --  Winning list 
   select 
   DECODE(count(distinct a.CGMC), null, 0,count(distinct a.CGMC)) num,
   b.jzgh,
   b.xymc
   from 
   DC.JSXXB b
   left join 
   MODEL.HJMD a 
   on a.ZYWCR=b.jzgxm
   where b.GWLX=' Full-time teacher '
   group by b.jzgh,b.xymc  

expand

Teachers with the same title All the teachers in the school How to modify ??