Count number of distinct values based on another set of distinct values

Question

I am trying to count a set of values based on another distinct value set:

-----------------------------------------------------    
|CusID  |Agent1  |Agent2 |Agent 3 |Agent 4 |Agent 5 |
-----------------------------------------------------
|1      |Pat     |Pat    |Jane    |Simon   |Jane    |
|2      |Pater   |Pat    |Simon   |Simon   |Pat     |
|1      |Jane    |Jane   |Simon   |Pat     |Pat     |
|3      |Simon   |Simon  |Jane    |Pat     |Peter   |
|3      |Jane    |Simon  |Pat     |Pater   |Pat     |
-----------------------------------------------------

I want to get:

----------------------------------------------------------------------------
|CusIDUnq  |AgentName|Count|AgentName|Count|AgentName|Count|AgentName|Count|
----------------------------------------------------------------------------
|1         |Pat      |4    |Jane     |4    |Simon    |2    |Peter    |0    |
|2         |Pat      |2    |Jane     |0    |Simon    |2    |Pater    |1    |
|3         |Pat      |3    |Jane     |2    |Simon    |3    |Peter    |2    |
----------------------------------------------------------------------------

How can I do this using SQL?

Thanks in advance!

edit: I need to tell that the number of customers and agents could change over time. So, I think a solution like below is also fine with me. The point is, I can't code the agent names or customer IDs into the query.

-----------------------
|CusID|AgentName|Count|
-----------------------
|1    |Pat      |4    |
|1    |Jane     |4    |
|1    |Simon    |2    |
|2    |Pat      |2    |
|2    |Simon    |2    |
|2    |Peter    |1    |

etc. Here, I need to omit 0 results where an agent is not listed for a specific customer.

Again, many thanks!

If you want a general solution which can support any number of agent names (e.g. more than 4, which is what your current data set has), then you'll need to delve into using dynamic SQL. In any case, please tell us which database you are using (e.g. MySQL, SQL Server, Oracle, Postgres, etc.). — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 28, 2019 at 14:55
And how are these count values defined? The logic is not clear. — Gordon Linoff
– Gordon Linoff, Commented Feb 28, 2019 at 14:59
@GordonLinoff Its the count of the number of times 'Pat' (or whatever) appears in any of the Agent fields for that ID — codeulike
– codeulike, Commented Feb 28, 2019 at 15:40

Gordon Linoff · Accepted Answer · 2019-02-28 15:02:00Z

3

If I understand correctly, you can do:

select id,
       sum(case when 'Pat' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_pat,
       sum(case when 'Jane' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_jane,
       sum(case when 'Simon' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_simon,
       sum(case when 'Peter' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_peter
from t
group by id;

This puts the counts in column names with the agent name, but that seems to be what you want.

answered Feb 28, 2019 at 15:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Himanshu Over a year ago

so close to your answer but still i think something more needs to be added in the query can check my answer and suggest to what i may be missing. i guess in my answer i need to add case statement in count as well like count( Case when Agent1='Pat' then Agent1) i think it should work without case but still unsure

Thorsten Kettner · Accepted Answer · 2019-02-28 16:52:06Z

In your data model a cus record has exactly five agents and there is a priority (or timeline or the like) of agents, with one agent being Agent1, one agent being Agent2, and so on.

In your query, however, you don't want to treat the agents differently, you don't even care whether they stem from the same cus row. In order to treat them all alike, we'll convert your table to a mere cus-agent table where each row has a pair of cus and agent. Then we can simply aggregate and count.

select cusid, agent, count(*)
from
(
  select cusid, agent1 as agent from mytable
  union all
  select cusid, agent2 as agent from mytable
  union all
  select cusid, agent3 as agent from mytable
  union all
  select cusid, agent4 as agent from mytable
  union all
  select cusid, agent5 as agent from mytable
) cus_agent
group by cusid, agent
order by cusid, agent;

If you want separate columns rather than rows for the agents, then use above query and care about the layout in your GUI layer (i.e. use a loop in your app or Website).

Himanshu · Accepted Answer · 2019-02-28 15:43:09Z

After messing the logic I have got some result as below query

     SELECT CUST_ID, 'Pat'
     Case when 'Pat' IN (Agent1,Agent2,
       Agent3,Agent4,Agent5)
      then 
      count(Agent1)+count(Agent2)+
         count(Agent3)+count(Agent4)+
          count(Agent5)
           else 0;
        End As 'Count_Pat',
      'Jane',

       Case when 'Jane' IN 
       (Agent1,Agent2,
       Agent3,Agent4,Agent5)
      then 
      count(Agent1)+count(Agent2)+
         count(Agent3)+count(Agent4)+
          count(Agent5)
           else 0;
        End as 'Count_Jane',
    'Simon',
     Case when 'Simon' IN 
       (Agent1,Agent2,
       Agent3,Agent4,Agent5)
      then 
      count(Agent1)+count(Agent2)+
         count(Agent3)+count(Agent4)+
          count(Agent5)
           else 0;
        End As 'Count_Simon',
         'Peter',
     Case when 'Peter' IN 
       (Agent1,Agent2,
       Agent3,Agent4,Agent5)
      then 
      count(Agent1)+count(Agent2)+
         count(Agent3)+count(Agent4)+
          count(Agent5)
           else 0;
        End as 'Count_Peter',

       From Table 

       group by cust_id;

Collectives™ on Stack Overflow

Count number of distinct values based on another set of distinct values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related