MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.26


CREATE TABLE listings
(
  id INTEGER NOT NULL PRIMARY KEY,
  status VARCHAR (50),
  agent_id INTEGER,
  updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  refno VARCHAR (50)
);
INSERT INTO listings VALUES
 (3, 'Y','2','2021-10-02','LP01'), (4, 'T','2','2021-05-01','LP02'),
 (5, 'S','5','2020-10-01','LP03'), (6, 'N','8','2021-10-06','LP06'), 
 (10, 'D','8','2021-10-06','LP05'), (11, 'D','6','2021-01-01','LP04'),
 (110, 'D',NULL,'2021-01-01','LP08');
CREATE TABLE agents(
	id INTEGER NOT NULL PRIMARY KEY,
	name VARCHAR (50)
);
INSERT INTO agents VALUES
 (2,'Jag'),(5,'Arnold'),(6,'Ricardo'),(8,'Mark');
SELECT
    CASE WHEN COALESCE(t.agent_id, 0) = 0
         THEN 'Unassigned' ELSE t.agent_id END AS agent_id,
    SUM(t.status = 'D') AS draft,
    SUM(t.status = 'N') AS unpublish,
    SUM(t.status = 'Y') AS publish,
    SUM(t.status = 'S') AS sold,
    SUM(t.status = 'T') AS let
FROM listings t 
INNER JOIN agents c
    ON t.agent_id = c.id
GROUP BY 1;


agent_id
draft
unpublish
publish
sold
let


2
0
0
1
0
1

5
0
0
0
1
0

8
1
1
0
0
0

6
1
0
0
0
0


SELECT
    SUM(t.status = 'D') AS draft,
    SUM(t.status = 'N') AS unpublish,
    SUM(t.status = 'Y') AS publish,
    SUM(t.status = 'S') AS sold,
    SUM(t.status = 'T') AS let, 
    COALESCE(c.name,'Unassigned')
FROM listings t 
LEFT JOIN agents c
    ON t.agent_id = c.id
GROUP BY
    COALESCE(c.name,'Unassigned')
    


draft
unpublish
publish
sold
let
COALESCE(c.name,'Unassigned')


0
0
1
0
1
Jag

0
0
0
1
0
Arnold

1
1
0
0
0
Mark

1
0
0
0
0
Ricardo

1
0
0
0
0
Unassigned