Select columns count from same table

In my mysql table having fields: id, ref, agent, status, date there are duplicated records like below:

Ref Agent Status Date
R-001 A1 New 2015-10-10
R-002 A1 New 2015-10-10
R-003 A1 Deleted 2015-10-10
R-004 A1 Deleted 2015-10-10
R-005 A1 New 2015-10-10
R-006 A2 New 2015-10-10
R-007 A2 Deleted 2015-10-10
R-008 A2 New 2015-10-10

I want to bring records as under so each agent shows count of each status:
A1, New, 3, Deleted, 2
A2, New, 2, Deleted, 1

Can you provide an example of the output you expect to see when given those entries in your table?

Output i would like:

A1, New, 3, Deleted, 2
A2, New, 2, Deleted, 1

I think this works

MySQL - http://sqlfiddle.com/#!9/a7800/8

  SELECT info.Agent, t1.Total AS New_Total, t2.Total AS Deleted_Total
  FROM #Info info
  LEFT JOIN 
    (SELECT Agent, `Status`, COUNT(*) AS Total 
      FROM #Info
      GROUP BY Agent, `Status`
      HAVING `Status` = 'New') AS t1 ON info.Agent = t1.Agent
  LEFT JOIN
    (SELECT Agent, `Status`, COUNT(*) AS Total 
      FROM #Info
      GROUP BY Agent, `Status`
     HAVING `Status` = 'Deleted') AS t2 ON info.Agent = t2.Agent
  GROUP BY info.Agent, t1.Total, t2.Total

SQL Server

  SELECT info.Agent, t1.Total AS New_Total, t2.Total AS Deleted_Total
  FROM #Info info
  LEFT JOIN 
    (SELECT Agent, [Status], COUNT(*) AS Total 
      FROM #Info
      GROUP BY Agent, [Status]
      HAVING [Status] = 'New') AS t1 ON info.Agent = t1.Agent
  LEFT JOIN
    (SELECT Agent, [Status], COUNT(*) AS Total 
      FROM #Info
      GROUP BY Agent, [Status]
     HAVING [Status] = 'Deleted') AS t2 ON info.Agent = t2.Agent
  GROUP BY info.Agent, t1.Total, t2.Total

You can do something similar to this as well…

SELECT Agent
     , COUNT(CASE WHEN Status='New' THEN 1 ELSE NULL END) AS CountNew
     , COUNT(CASE WHEN Status='Deleted' THEN 1  ELSE NULL END) AS CountDeleted
  FROM RefTable
 GROUP BY Agent;
1 Like

your use of HAVING is inefficient – WHERE would be a lot better

also, you’ve completely flipflopped the new and deleted totals

Excellent, many thanks for the great replies.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.