SQLserver AVG函数求1到3月的各项平均值

Question

我写了一部分可以求1到3月的总和，求平均值时数据不对
sql语句如下
select
SUM(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.DYM else 0 end) DYM1,
SUM(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.RY else 0 end) RY1,
SUM(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.DYM else 0 end) DYM2,
SUM(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RY else 0 end) RY2,
SUM(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RYOU else 0 end) RYOU2,
SUM(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.DYM else 0 end) DYM3,
SUM(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.RY else 0 end) RY3,
from DXTJ A left join GLY_JBXXB B on A.GLY=B.ID left join DEPT_JBXXB C on A.SGDW=C.DEPEID left join CSJING_SCSJB D on A.JH=D.JH
where JB='油井'
数据库格式如下

Answer 1

你把你的else 0都删掉试试。

select
avg(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.DYM  end) DYM1,
avg(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.RY  end) RY1,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.DYM  end) DYM2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RY  end) RY2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RYOU  end) RYOU2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.DYM  end) DYM3,
avg(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.RY  end) RY3,
from DXTJ A left join GLY_JBXXB B on A.GLY=B.ID left join DEPT_JBXXB C on A.SGDW=C.DEPEID left join CSJING_SCSJB D on A.JH=D.JH
where JB='油井'

avg不对的原因是,它的算法是求和再除以非空的行数,你这行数没有发生变化,不是指定月份的数据你给了个0,它当然数据会偏小了

另外,你确定不在后面加个where条件限定下查询数据的范围么？你这样查,整个表的数据都扫到了