在SQL中平均空库伦

问题描述

嗨
我有一个使用的存储过程，我想平均空库仑数:我的存储过程

Hi
I have a stored procedure that I use and i want to average null coulmns: my stored procedure

ALTER procedure [dbo].[Technology] 
@portRef int 
as
SELECT avg(planned)as Planned,avg(achieved)  as Achieved 
FROM Port inner join Technology on Port.portID = Technology.portRef 
WHERE portRef =@portRef
OR Technology.ShowAll =@portRef

所以我使用数据源将此存储过程绑定到图表，并且当列为空时，C#代码将抛出此错误
值对于小数而言太大或太小.
我如何处理存储到这些空列的平均值

so i bind this stored procedure to a chart using datasource and when the column is null the c# code throw this error
Value was either too large or too small for a Decimal.
how can I handle my stored to avg those null column

推荐答案

case avg(planned)
when null then 0
else avg(planned)
end

SELECT 
AVG(ISNULL(planned, 0)) as Planned, 
avg(ISNULL(achieved,0)) as Achieved
FROM Port inner join Technology on Port.portID = Technology.portRef
WHERE portRef =@portRef OR Technology.ShowAll =@portRef

SELECT avg(COALESCE(planned,0))as Planned,avg(COALESCE(achieved,0)) as Achieved...

在第二种情况下，您可以使用
过滤选择

In the second case you can filter selection with

... WHERE planned is not null

但这样一来，您每次选择只能计算一个平均值.但这在存储过程中并不是实际问题.

but this way you can calculate only one average per select. But this is no actual problem in a stored procedure.

这篇关于在SQL中平均空库伦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！