COALESCE not returning an extra row for Null and 0 values-CodePudding

Currently I have 2 tables, the first table shows a count of statuses, refno. and agent_id(person in charge of the refno.) and the second table has an id and agent_name. So to refer a particular agent next to the refno. in table 1, you can reference it via the id of the agent table.

Dbfiddle:

CodePudding user response：

You need a LEFT join of listings to agents if you want in the results the rows of listings that do not have a matching id in agents.
Also, you must group by COALESCE(t.agent_id, 0) to cover both cases of 0 and null in agent_id:

SELECT COALESCE(c.name, 'Unassigned') name,
       SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft, 
       SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish, 
       SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish, 
       SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action, 
       SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted, 
       SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold, 
       SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
       SUM(CASE WHEN t.status IN ('D', 'N', 'Y', 'U', 'L', 'S', 'T') THEN 1 ELSE 0 END) AS total 
FROM listings t LEFT JOIN agents c 
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;

I added an ELSE 0 part in all CASE expressions so that you get 0s in the results instead of NULLs and changed the expression to just 1 SUM for the column total by using the operator IN, but if 'D', 'N', 'Y', 'U', 'L', 'S' and 'T' are the only possible values of status then instead you can just use COUNT(*):

SELECT COALESCE(c.name, 'Unassigned') name,
       SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft, 
       SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish, 
       SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish, 
       SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action, 
       SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted, 
       SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold, 
       SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
       COUNT(*) AS total 
FROM listings t LEFT JOIN agents c 
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;

See the demo.