在下面的情况下sum要如何用？

[ccssme]

(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
通过上面的语句取得下面的表
MemberID num
1 3
1 2
2 1

我要得到下面的表
MemberID num
1 5
2 1
我要怎么办？？

[txlicenhe]

Select memberID,sum(num) as num from
(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) temp
group by MemberID

[welyngj]

select MemberID,sum(num) from
((select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) a group by
memberid

[yujohny]

select memberID,sum(num) num from
(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) A
group by MemberID

[jastion]

CREATE table aa (a varchar(10) ,b int)
insert into aa values('1',1)
insert into aa values('1',2)
insert into aa values('1',3)
insert into aa values('2',2)
insert into aa values('2',3)

CREATE table bb (a varchar(10) ,b int)
insert into bb values('1',4)
insert into bb values('1',5)
go
--創建視圖
CREATE View v_aabb
as
select a,sum(b) as b from aa group by a
union
select a,sum(b) as b from bb group by a
go
select a,sum(b) as b from v_aabb group by a

[ccssme]

(SELECT num
FROM (SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num from(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) as A
group by MemberID) T
ON Forums_Replies.MemberID = T.MemberID) R
WHERE Forums_Replies.ReplyID = R.ReplyID) AS Num

这一句是上面那一句的扩展，其中R表为
MemberID ReplyID num
1 134 5
1 135 5
2 136 4
可是 (Select num from (。。。)R where Replies.ReplyID=R.ReplyID)AS num
就报错，什么地方错了呢？

[aierong]

select MemberID,sum(num) num
from (
select MemberID,Count(*) num
from Replies
group by Replies.MemberID
union
select MemberID,Count(*) num
from Topics
group by Topics.MemberID)) a
group by MemberID

[ccssme]

报的是GROUP BY子句出错

[aierong]

你的连接条件写错了把
你是Replies和t表连接
怎么是ON Forums_Replies.MemberID = T.MemberID







SELECT num
FROM (
SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num
from(
select MemberID,Count(*) num from Replies group by Replies.MemberID
union
select MemberID,Count(*) num from Topics group by Topics.MemberID) as A
group by MemberID) T
ON Replies.MemberID = T.MemberID ) R
WHERE Forums_Replies.ReplyID = R.ReplyID) AS Num

[dafu71]

select MemberID, sum(num) as num from (
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) as tmp group by MemberID

[ccssme]

是我贴上来时改错的，本地没有错，谢谢，除去这个原因还有什么地方有错呢？

(SELECT num
FROM (SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num from(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) as A
group by MemberID) T
ON Replies.MemberID = T.MemberID) R
WHERE Replies.ReplyID = R.ReplyID) AS Num

[ccssme]

yujohny(踏网无痕) :
在用你给的
select memberID,sum(num) num from
(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) A
group by MemberID

语句当
MemberID num
1 3
1 3
2 1

会得到下面的表，而这时当MemberID=1时num应该为6而不是为3？？？
MemberID num
1 3
2 1

[lijinqiang]

SELECT MemberID, SUM(num) FROM (
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID))TEMPTABLE
GROUP BY MemberID

[lvltt]

SELECT num
FROM (
SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num
from(
select MemberID,Count(*) num from Replies group by Replies.MemberID
union
select MemberID,Count(*) num from Topics group by Topics.MemberID) as A
group by MemberID) T
ON Replies.MemberID = T.MemberID ) R
WHERE Forums_Replies.ReplyID = R.ReplyID) AS Num

[orcale]

select Memberid,sum(num) as num from ((select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) a
group by a.MemberID