Jennifer Zou Jennifer Zou - 3 years ago 116
SQL Question

Access SQL: How to group by from a view (subquery in Access SQL)?

I have a table which has the following columns:

-Agent Name -Ticket ID -Requester's Email

How can I get Agent name along with the number of customers who submit more than 1 tickets?

Example Result:

  • Jennifer 2

  • Mike 6

Means that Agent Jennifer takes care of 2 customers who submitted more than 1 tickets, Mike takes care of 6 such customers.
I am using MS Access....

What I can do is:
SELECT agent alias, Count(email) AS [CountOfvisitor email]
FROM table
GROUP BY agent alias, email
HAVING (((Countemail)>1));

But this gives me something like:

  • Jennifer 3

  • Jennifer 6

  • Jennifer 2


Answer Source

Try This


select agentName,count(*) from (
  Select AgentName,email,count(*) from TableName 
  group by AgentName,email
  having count(*)>1
)t group by agentName
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download