开发者

SQL Server JOIN adds results?

开发者 https://www.devze.com 2023-02-15 00:19 出处:网络
I have a table which basically contains a Person_ID, Action, TimeStamp and using Microsoft ReportBuilder I have a report which tables each Person and the COUNT for their Actions.

I have a table which basically contains a Person_ID, Action, TimeStamp and using Microsoft ReportBuilder I have a report which tables each Person and the COUNT for their Actions.

Person     Action X       ActionY
1             3              5
2             0              4

Now I need to filter the results to only show people in a certain group which is defined by another table containing Person_ID, Group_ID.

When I do a JOIN and filter results based on the Group_ID = x the counts are very high, although it does filter correctly.

I run the query manually in SQL Server Manager and it is returning the same row multiple times?

EDIT:

My current SQL is

select开发者_运维问答 t1.personid, t1.action, t2.personid, t2.groupid 
from t1 
inner join t2 on t1.personid = t2.personid 
where t2.groupid = 1

This returns each line multiple times, forgetting the count part as this is in the report builder I would like to understand why the same row is returned multiple times as this is what breaks the report.


Do a DISTINCT

SO..

SELECT PersonID, ActionX = COUNT(distinct varname), ActionY = SUM(distint varname)
FROM tblName1 a
INNER JOIN tblName2 b ON a.PersonID = b.PersonID
WHERE b.Group_ID = 'groupvar' 
0

精彩评论

暂无评论...
验证码 换一张
取 消