急救！这样的情况真叫人头痛！！！

[usxue]

在SQL SERVER 2000中的SQL语句：

select case when sum(D3) is null then 0.0 else convert(float(8),sum(D3))/avg(days) end 'kk' from DS_DigInto where Y=2005 and M<=2 and S3='半' group by M

返回的记录为空，不是NULL，如果将group by M去掉后就是0.0了，
但这是不行的，怎样解决这样的情况：
让它返回0.0，但不要去掉 group by M，急急急！！！
谢谢各位了！

[zjcxc]

--我上面的不对,改成这样:

select kk
from(
select isnull(convert(float(8),sum(D3))/avg(days),'0.00') as 'kk',bz=0
from DS_DigInto
where Y=2005 and M<=2 and S3='半' group by M
union all
select '0.00' as 'kk',bz=case
when exists(select * from DS_DigInto where Y=2005 and M<=2 and S3='半')
then 1 else 0 end
)a where bz=0

[zjcxc]

--下面用系统表来做测试

--满足条件的记录数
select count(*) from syscolumns where id<10

--旧写法
select isnull(convert(float(8),sum(colid))/avg(colid),'0.00') as 'kk'
from syscolumns
where id<10 group by id

--新写法
select kk
from(
select isnull(convert(float(8),sum(colid))/avg(colid),'0.00') as 'kk',bz=0
from syscolumns
where id<10 group by id
union all
select '0.00' as 'kk',bz=case
when exists(select * from syscolumns where id<10)
then 1 else 0 end
)a where bz=0

/*--结果
-----------
131

（所影响的行数为 1 行）

kk
-----------------------------------------------------
25.0
29.0
33.0
21.0
11.0
5.0
11.0

（所影响的行数为 7 行）

kk
-----------------------------------------------------
25.0
29.0
33.0
21.0
11.0
5.0
11.0

（所影响的行数为 7 行）
--*/

[zjcxc]

--换个查询记录数为0的条件测试

--满足条件的记录数
select count(*) from syscolumns where 100<10

--旧写法
select isnull(convert(float(8),sum(colid))/avg(colid),'0.00') as 'kk'
from syscolumns
where 100<10 group by id

--新写法
select kk
from(
select isnull(convert(float(8),sum(colid))/avg(colid),'0.00') as 'kk',bz=0
from syscolumns
where 100<10 group by id
union all
select '0.00' as 'kk',bz=case
when exists(select * from syscolumns where 100<10)
then 1 else 0 end
)a where bz=0

/*--测试结果


-----------
0

（所影响的行数为 1 行）

kk
-----------------------------------------------------

（所影响的行数为 0 行）

kk
-----------------------------------------------------
0.0

（所影响的行数为 1 行）
--*/

[jiang130]

select convert(float(8),sum(isnull(D3,0)))/avg(days) 'kk' from DS_DigInto where Y=2005 and M<=2 and S3='半' group by M

[Softlee81307]

select case when sum(isnull(D3,0))=0 then 0.0 else convert(float(8),sum(D3))/avg(days) end 'kk' from DS_DigInto where Y=2005 and M<=2 and S3='半' group by M

[usxue]

Very Good!非常好！谢谢！！！！

[dazhu2]

d

[xuam]

select convert(float(8),sum(isnull(D3,0)))/avg(days) from DS_DigInto where Y=2005 and M<=2 and S3='半' group by M

[fesxe]

sum(D3) is null then 0.0 else convert(float(8),sum(D3))/avg(days)
换成
convert(sum(isnull(d3,0)) )/avg(days)

[zjcxc]

select isnull(convert(float(8),sum(D3))/avg(days),'0.00') as 'kk'
from DS_DigInto
where Y=2005 and M<=2 and S3='半' group by M

[jones129]

select isnull(convert(float(8),sum(D3))/avg(days),0)
from DS_DigInto where Y=2005 and M<=2 and S3='半' group by M