pr2-eugin
Super Moderator
- Local time
- Today, 04:31
- Joined
- Nov 30, 2011
- Messages
- 8,494
Count only one record per group
The system started with was nothing but a historic information of who was called, when and by who, along with the call count.. So the table structure being..
dialerCountTbl:
dial_ID - AutoNumber
agent_ID - Number (FK)
rawID - Number (FK)
dialCount - Number
dialDate - Date/Time
Sample data inside like..
So what I would like the count to give me is,
Well a simple GroupBy clause will return result like..
So technically I need is a count that will include the RAW ID for that group only once.. So RAW ID 123 will be counted only once for Agent 83..
The dial count will be increasing starts with 2 and goes up to a maximum of 5.. One way to correct Agent 83 will be, if I filter by dialCount = 2, then the result will be correct for agent 83 but will result 0 for agent 88..
Does that make any sense?? Or should I take another shot in explaining?
The system started with was nothing but a historic information of who was called, when and by who, along with the call count.. So the table structure being..
dialerCountTbl:
dial_ID - AutoNumber
agent_ID - Number (FK)
rawID - Number (FK)
dialCount - Number
dialDate - Date/Time
Sample data inside like..
Code:
dial_ID agent_ID rawID dialCount dialDate
1 83 [COLOR=Red]123[/COLOR] 2 12/12/2012
2 3 [COLOR=SandyBrown]145[/COLOR] 2 12/12/2012
3 88 [COLOR=YellowGreen]412[/COLOR] 3 12/12/2012
4 83 [COLOR=Red]123[/COLOR] 3 13/12/2012
78 83 [COLOR=Red]123[/COLOR] 5 15/12/2012
120 88 [COLOR=SandyBrown]145[/COLOR] 3 15/12/2012
Code:
agent_ID Count(rawID)
3 1
83 1
88 2
Code:
agent_ID Count(rawID)
3 1
83 3
88 2
The dial count will be increasing starts with 2 and goes up to a maximum of 5.. One way to correct Agent 83 will be, if I filter by dialCount = 2, then the result will be correct for agent 83 but will result 0 for agent 88..
Does that make any sense?? Or should I take another shot in explaining?
Last edited: