By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
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');
Records: 7 Duplicates: 0 Warnings: 0
CREATE TABLE agents(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR (50)
);
INSERT INTO agents VALUES
(2,'Jag'),(5,'Arnold'),(6,'Ricardo'),(8,'Mark');
Records: 4 Duplicates: 0 Warnings: 0
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 |