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'
精彩评论