Как добавить нулевое значение в group_concat, если запись не существует?

Используйте else 0 в case expression

SELECT *, GROUP_CONCAT(lid) as lids, GROUP_CONCAT(pyear) as pyears, 
 COUNT(DISTINCT lib_id) AS total_count,
 CASE WHEN COUNT(*)>=1 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(if (total is null,0,total) ORDER BY lid ASC SEPARATOR ' '),' ',1),' ',-1) else 0 END AS P1, 
 CASE WHEN COUNT(*)>=2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(if (total is null,0,total) ORDER BY lid ASC SEPARATOR ' '),' ',2),' ',-1) else 0 END AS P2, 
 CASE WHEN COUNT(*)>=3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(if (total is null,0,total) ORDER BY lid ASC SEPARATOR ' '),' ',3),' ',-1) else 0 END AS P3
 FROM ( 
  SELECT * FROM view_items WHERE lid='1' 
  UNION 
  SELECT * FROM view_items WHERE lid='2' 
  UNION
  SELECT * FROM view_items WHERE lid='3' 
 ) AS AZ GROUP BY class_id

Чтобы получить 0 значений, где в таблице нет lid, вам нужно создать список всех значений lid для всех значений class_id, что вы можете сделать с CROSS JOIN из двух запросов SELECT DISTINCT (один для lid, один для class_id). Затем это можно LEFT JOINзанести в таблицу, чтобы получить требуемое total значение для каждой P группы с помощью условной агрегации:

SELECT c.class_id,
       MAX(v.class),
       GROUP_CONCAT(COALESCE(v.lid, 0) ORDER BY l.lid) AS lids,
       MAX(CASE WHEN v.lid=1 THEN total ELSE 0 END) AS P1,
       MAX(CASE WHEN v.lid=2 THEN total ELSE 0 END) AS P2,
       MAX(CASE WHEN v.lid=3 THEN total ELSE 0 END) AS P3
FROM (SELECT DISTINCT lid FROM view_items) l
CROSS JOIN (SELECT DISTINCT class_id FROM view_items) c
LEFT JOIN view_items v ON v.lid = l.lid AND v.class_id = c.class_id
GROUP BY c.class_id

Выход:

class_id    class       lids    P1      P2      P3
73          Leader      1,2,3   10000   20000   30000
76          Staff       0,2,0   0       8000    0
77          Consultant  1,2,0   8000    10000   0
78          Team Leader 0,0,3   0       0       8000
83          Coordinator 1,0,0   6000    0       0

Демо на dbfiddle

Я думаю, проблема в том, что вы говорите, что count >= 1, но count >= 3 также является count >=1, поэтому ваш код никогда не достигает этой строки. Ты должен сказать, СЛУЧАЙ, КОГДА COUNT() >=1 И СЧИТАТЬ () < 2... СЛУЧАЙ, КОГДА COUNT() >=2 И СЧЕТ () < 3...

Я не думаю, что GROUP_CONCAT() это правильный подход к тому, что вы хотите. Попробуй это:

SELECT vi.class_id, vi.class,
       COUNT(DISTINCT vi.lib_id) AS total_count,
       CONCAT_WS(',',
                 MAX(CASE WHEN vi.lid = 1 THEN 1 ELSE 0 END),
                 MAX(CASE WHEN vi.lid = 2 THEN 1 ELSE 0 END),
                 MAX(CASE WHEN vi.lid = 3 THEN 1 ELSE 0 END)
                ) as lids,
       SUM(CASE WHEN vi.lid = 1 THEN vi.total ELSE 0 END) as total_1,
       SUM(CASE WHEN vi.lid = 2 THEN vi.total ELSE 0 END) as total_2,
       SUM(CASE WHEN vi.lid = 3 THEN vi.total ELSE 0 END) as total_3
FROM view_items vi
WHERE vi.lid IN (1, 2, 3) 
GROUP BY vi.class_id;

Примечания:

• Ваш подзапрос и UNION не нужны. В MySQL они могут на самом деле иметь производительность причинить боль.
• Я предполагаю, что lid — это число, поэтому я удалил одинарные кавычки.
• Вы можете использовать условную агрегацию для каждой нужной суммы. Парсинг GROUP_CONCAT() не является правильным способом сделать это.
• Ваш вопрос касается lids. CONCAT_WS() делает то, что вы хотите — объединяет либо значение (если оно появляется), либо ноль, если оно не появляется.