Search code examples
sqlsql-server

How to get count based on agent in multiple columns

I have data like below in a table.

CaseNum FirstAgent NextAgent
1234 Rama Raja
1234 Rama Rama
5678 Siva Rama
5678 Siva Krishna

My requirement is I need to output like below.

Agent Case Count
Rama 2
Raja 1
Siva 1
Krishna 1

I need to show both firstagent and nextagent in a single column. We should not consider duplicates if the same agent is there in first agent and next agent.

Could some on please help on this to write the query.

Solution

  • Here is a way to do it using UNION ALL and GROUP BY :

    SELECT Agent, COUNT(DISTINCT CaseNum) AS [Case Count]
FROM (
  SELECT FirstAgent As Agent, CaseNum
  FROM mytable
  UNION ALL
  SELECT NextAgent, CaseNum
  FROM mytable
) AS S
GROUP BY Agent

    Demo here