SELECT arch.dept_id ,dept.dept_name,COUNT(*) AS arch_sum,

SUM(CASE arch_status WHEN 0 THEN 1 ELSE 0 END) in_stock,

SUM(CASE arch_status WHEN 1 THEN 1 ELSE 0 END) on_loan,

SUM(CASE arch_status WHEN 2 THEN 1 ELSE 0 END) in_lose,

SUM(CASE arch_status WHEN 3 THEN 1 ELSE 0 END) to_be_expire,

SUM(CASE arch_status WHEN 4 THEN 1 ELSE 0 END) in_scrap

FROM T_ARCH arch

LEFT JOIN DB_ZJMF.T_ORG_DEPT dept  ON arch.dept_id = dept.id

WHERE arch.is_delete = 0

GROUP BY arch.dept_id