select
A.[县区],
[考生总数]=(select count(1) from 表),
[有效人数]=(select count(1) from 表 where [考生总分]>0),
[语文平均分]=((select avg(语文成绩) from 表)+0.00)/]=(select count(1) from 表),
[语文及格率]=((select count(1) from 表 where [语文成绩]>60)+0.00)/]=(select count(1) from 表),
[数学平均分]=((select avg(数学成绩) from 表)+0.00)/]=(select count(1) from 表),
[数学及格率]=((select count(1) from 表 where [数学成绩]>60)+0.00)/]=(select count(1) from 表)
from 表 A
A.[县区],
[考生总数]=(select count(1) from 表),
[有效人数]=(select count(1) from 表 where [考生总分]>0),
[语文平均分]=((select avg(语文成绩) from 表)+0.00)/]=(select count(1) from 表),
[语文及格率]=((select count(1) from 表 where [语文成绩]>60)+0.00)/]=(select count(1) from 表),
[数学平均分]=((select avg(数学成绩) from 表)+0.00)/]=(select count(1) from 表),
[数学及格率]=((select count(1) from 表 where [数学成绩]>60)+0.00)/]=(select count(1) from 表)
from 表 A
县区,
考生总数 = count(*),
有效人数 = sum(case when isnull(考生总分,0)>0 then 1 else 0 end),
语文平均分 = avg(语文成绩),
语文及格率 = sum(case when isnull(语文成绩,0)>=60 then 1 else 0 end)/(count(*)+0.0),
数学平均分 = avg(数学成绩),
数学及格率 = sum(case when isnull(数学成绩,0)>=60 then 1 else 0 end)/(count(*)+0.0)
from
表
group by
县区