--> 测试数据：[sample]
if object_id('[sample]') is not null drop table [sample]
create table [sample]([id] int,[date] datetime,[user_id] varchar(3),[result] int)
insert [sample]
select 0,'12.05.01','001',0 union all
select 1,'12.05.01','001',1 union all
select 2,'12.05.01','001',0 union all
select 3,'12.05.01','001',0 union all
select 4,'12.05.01','002',0 union all
select 5,'12.05.01','002',0 union all
select 6,'12.05.01','003',1 union all
select 7,'12.05.01','003',0

with t
as(
select [date],[user_id],[result] from [sample] a 
where exists(
select 1 from  [sample] b where [result]=1  and a.[user_id]=b.[user_id]
)
)

select [date],[user_id],
(select COUNT(*) from t where result=0)/(select COUNT(distinct [user_id]) from t) 
as AvgTimes
from t b
group by [date],[user_id]
/*
date    user_id    AvgTimes
2012-05-01 00:00:00.000    001    2
2012-05-01 00:00:00.000    003    2
*/


--怎么都不会是你给的那个结果

------解决方案--------------------

SQL code

---------这个是统计至少1次成功的用户，登录失败的条数
select date,userid
from #tbs where userid in(
select userid from #tbs where flag=1 group by date,userid having count(*)>0)
and flag=0 
------------------------------------------------
date                 userid
-------------------- --------------------
12.05.01             001
12.05.01             001
12.05.01             001
12.05.01             003

(4 行受影响)

----------------------------------------------------
-------这个是统计人数
select count(*) from(select date,userid
    from #tbs where userid in(
    select userid from #tbs where flag=1 group by date,userid having count(*)>0)
    and flag=0 group by date,userid)tmp

------------------------------------------------
---------这个是条数，除以人数等于平均数
select date,count(*)/
(
    select count(*) from(select date,userid
    from #tbs where userid in(
    select userid from #tbs where flag=1 group by date,userid having count(*)>0)
    and flag=0 group by date,userid)tmp
)avgTimes
from #tbs where userid in(
select userid from #tbs where flag=1 group by date,userid having count(*)>0)
and flag=0 group by date
 --------------------------------------
date                 avgTimes
-------------------- -----------
12.05.01             2

(1 行受影响)

----------------------
---你可以把#tbs改成你的表名，字段换成你的字段，跑一次，就知道了。

-----------
2

(1 行受影响)