A very messy Count. (1 Viewer)

pr2-eugin

Super Moderator
Local time
Today, 15:30
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..
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
So what I would like the count to give me is,
Code:
agent_ID    Count(rawID)
3           1
83          1
88          2
Well a simple GroupBy clause will return result like..
Code:
agent_ID    Count(rawID)
3           1
83          3
88          2
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?
 
Last edited:

plog

Banishment Pending
Local time
Today, 10:30
Joined
May 11, 2011
Messages
11,611
You need a sub-query that Groups all rawID values by agent_ID values:

Code:
SELECT rawID, agent_ID FROM dialerCountTbl GROUP BY rawID, agent_ID;

Save that then use it as the source for your final count.
 

pr2-eugin

Super Moderator
Local time
Today, 15:30
Joined
Nov 30, 2011
Messages
8,494
Well plog, you're 'Da Bomb'.. :)

That was so simple, :eek: I thought I did try subquery but might have messed up the syntax and was fed up that I gave up trying again.. It works well for the test, hope it will work for the huge data.. Cheers for that..
 

Users who are viewing this thread

Top Bottom