【疑惑求解】不能对包含聚合或子查询的表达式执行聚合函数。

[crazyxingjc]

表信息：
表A
用户ID 用户名 上线用户(用户名)
111 AAA null
222 BBB AAA
333 CCC AAA
444 DDD null


表B
用户ID 在线时间(s)
111 2323
111 2323
222 5656
222 5656
333 782
333 656
444 569
444 856


查询结果：所有上线用户信息
用户ID 用户名 下线用户数 下线用户在线时间5000s以上人数


查询语句：
select b.upname,a.id,
count(b.id) 总下线人数,
count(case when sum(m.onlinetime) > 5000 then m.id end)
from tableA b
left join tableA a
on b.upname = a.name
left join tableB m
on b.id = m.id
where b.upname is not null
group by b.upname,a.id



错误：
第3行 不能对包含聚合或子查询的表达式执行聚合函数。

求大神指教

[Andy__Huang]

select b.upname,a.id,count(b.id) 总下线人数,
	count(case when m.onlinetime > 5000 then m.id end) 
from tableA b
left join tableA a on b.upname = a.name
left join (select id,SUM(onlinetime) as onlinetime from tableB group by id) m on b.id = m.id
where b.upname is not null
group by b.upname,a.id

[Andy__Huang]

create table #ta(id int,name varchar(10), upname varchar(100))
insert into #ta
select 111,'AAA',null
union all select 222,'BBB','AAA'
union all select 333,'CCC','AAA'
union all select 444,'DDD',null

create table #tb(id int, onlineTime int)
insert into #tb
select 111,2323
union all select 111,2323
union all select 222,5656
union all select 222,5656
union all select 333,782
union all select 333,656
union all select 444,569
union all select 444,856


select b.upname,a.id,count(b.id) 总下线人数,
	count(case when m.onlinetime > 5000 then m.id end) 上线人数
from #ta b
left join #ta a on b.upname = a.name
left join (select id,SUM(onlinetime) as onlinetime from #tb group by id) m on b.id = m.id
where b.upname is not null
group by b.upname,a.id


drop table #ta,#tb


/*
upname	id	总下线人数	上线人数
AAA	111	2	1
*/

[crazyxingjc]

引用 1 楼 hdhai9451 的回复:

select b.upname,a.id,count(b.id) 总下线人数,
	count(case when m.onlinetime > 5000 then m.id end) 
from tableA b
left join tableA a on b.upname = a.name
left join (select id,SUM(onlinetime) as onlinetime from tableB group by id) m on b.id = m.id
where b.upname is not null
group by b.upname,a.id

先对链接表做处理啊，被自己绕进去了,想一起搞定。多谢大神指教。