SQL query for getting count for grouped items

Question

I have a table with the following columns:

agent     status  

A          Mail Sent  
B          Fax Sent  
A          Fax Sent  
B          Mail Sent  
B          Mail Sent  
B          Fax Sent

I want to get this result:

Agent  Fax_Count    Mail_Count

A      1            1  
B      2            2

Answer 1

You can try something like this

DECLARE @TABLE TABLE(
    agent VARCHAR(10),
    status VARCHAR(10)
)

INSERT INTO @TABLE (agent,status) SELECT 'A','Mail Sent'
INSERT INTO @TABLE (agent,status) SELECT 'B','Fax Sent'
INSERT INTO @TABLE (agent,status) SELECT 'A','Fax Sent'
INSERT INTO @TABLE (agent,status) SELECT 'B','Mail Sent'
INSERT INTO @TABLE (agent,status) SELECT 'B','Mail Sent'
INSERT INTO @TABLE (agent,status) SELECT 'B','Fax Sent' 

SELECT  agent,
     SUM(CASE WHEN status = 'Mail Sent' THEN 1 ELSE 0 END) Mail_Count ,
     SUM(CASE WHEN status = 'Fax Sent' THEN 1 ELSE 0 END) Fax_Count
FROM    @TABLE
GROUP BY agent

Answer 2

  select Name, SUM(MailSent),SUM(FaxSent)
  from 
  (
  select 
   case Status when 'Mail Sent' then 1 else 0 end as MailSent , 
   case Status when 'Fax Sent' then 1 else 0 end as FaxSent  ,
   Name
   from Agents
  ) tmp

Answer 3

As an alternative to astander's (good) solution:

SELECT  agent,
        (SELECT COUNT(*) FROM myTable WHERE agent = t.agent AND status = 'Mail Sent') Mail_Count,
        (SELECT COUNT(*) FROM myTable WHERE agent = t.agent AND status = 'Fax Sent') Fax_Count
FROM    myTable t
GROUP BY agent

Depending on the distribution of your data, performance might be better than his solution (no SUM over a calculated field) or worse (sub-selects) or equal (if the query analyzer finds the optimal execution plan in both cases).

Answer 4

You could do this... probably not the most graceful but it would work... Just swap out @Table with the table you're querying...

SELECT DISTINCT
  T.AGENT,
  (SELECT COUNT(AGENT) FROM @TABLE WHERE AGENT = T.AGENT AND Status LIKE 'Fax%') AS Fax_Count,
  (SELECT COUNT(AGENT) FROM @TABLE WHERE AGENT = T.AGENT AND Status LIKE 'Mail%') AS Email_Count
FROM
  @TABLE T

Answer 5

This is the new way (since SQL Server 2005) to solve the problem by using PIVOT functionality:

SELECT agent, [Mail Sent] AS Mail_Count, [Fax Sent] AS Fax_Count
FROM 
(
  SELECT T.agent, T.status, COUNT(*) AS Counter
  FROM tblAgents AS T
  GROUP BY T.agent, T.status
) AS Data
PIVOT
(
  SUM(Counter) FOR [status] IN ([Mail Sent], [Fax Sent])
) AS PivotTable

I do not use this my self very often due to more complex syntax, but perhaps there is some performance to gain?