Query to display this output

russel · #1 (**permalink**) 06-30-11, 06:40

Table Name--CustomerInfo

HTML Code:

PhoneNo.                   AgentName             Status
------------------------------------------------------
8086817429                 Agent1                  RPC
8129001663                 Agent2                  NCT
8129008620                 Agent3                  NCT
8129011825                 Agent4                  RPC
8129012154                 Agent2                  BUS
8129016296                 Agent4                  RPC
8129016581                 Agent3                  BUS
8129024743                 Agent2                  NCT
8129032230                 Agent1                  NCT
8129033135                 Agent4                  BUS
8129043752                 Agent1                  RPC
8129078552                 Agent3                  RPC
8129567852                 Agent1                  BUS 
8129874122                 Agent2                  BUS
8129056982                 Agent4                  BUS

I need to know the query to display distinct AgentName in one column and count of their RPC status.

Result should be

HTML Code:

AgentName              Count of RPC
Agent1                          2
Agent2                          0
Agent3                          1
Agent4                          2

Please Help..........

r937 · #2 (**permalink**) 06-30-11, 07:19

Code:

SELECT AgentName 
     , COUNT(CASE WHEN status = 'RPC'
                  THEN 'whoopee'
                  ELSE NULL END) AS `Count of RPC`
  FROM CustomerInfo
GROUP
    BY AgentName

russel · #3 (**permalink**) 06-30-11, 07:40

thx a lot....it worked

Internet Services

Web Development

Digital Marketing

Consumer Tech