在SQL中平均空库伦 [英] averaging null coulmn in 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

推荐答案

您可以在存储过程中尝试大小写.

you can try case in stored procedure.

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

首先，您需要确定null值对您意味着什么:0或需要跳过:avg(null,1,2)==1或==1.5
在第一种情况下，您可以使用 COALESCE [

First of all, you need to decide what a null value should mean to you: 0 or need to be skipped: avg(null,1,2)==1 or ==1.5
In the first case you can use COALESCE[^], like this:

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中平均空库伦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！