新手困扰小弟我半天的sql语句

新手求教：困扰我半天的sql语句
希望各位能帮我

数据表如下：

id | date | user_id | result

sample数据如下：  

id | date | user_id | result
0 12.05.01 001 0 
1 12.05.01 001 1
2 12.05.01 001 0
3 12.05.01 001 0
4 12.05.01 002 0
5 12.05.01 002 0
6 12.05.01 003 1
7 12.05.01 003 0


问题如下：
该表存放的是每日用户的某操作，result=1表示操作成功，0表示失败
我现在要想统计的是 每天，所有有过操作成功用户的平均失败次数.比如上面所示，05.01这天只有001和003成功过，那么这天用户平均失败次数就是2（001失败3次，003失败1次）。

想得到的结果如下：

date | averageTimes
12.05.01 2
12.05.02 3.2
12.05.03 1.8
.....

希望能得到各位的指教，谢谢！

[解决办法]

SQL code

--> 测试数据：[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 allselect 1,'12.05.01','001',1 union allselect 2,'12.05.01','001',0 union allselect 3,'12.05.01','001',0 union allselect 4,'12.05.01','002',0 union allselect 5,'12.05.01','002',0 union allselect 6,'12.05.01','003',1 union allselect 7,'12.05.01','003',0with tas(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 AvgTimesfrom t bgroup by [date],[user_id]/*date    user_id    AvgTimes2012-05-01 00:00:00.000    001    22012-05-01 00:00:00.000    003    2*/--怎么都不会是你给的那个结果
[解决办法]
SQL code
---------这个是统计至少1次成功的用户，登录失败的条数select date,useridfrom #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             00112.05.01             00112.05.01             00112.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)avgTimesfrom #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 行受影响)