GROUP BY count also missing types (Maria DB)-CodePudding

select kyc_type, count(*)
from kyc_table
where YEAR(created_at) = 2020
GROUP BY(kyc_type);

there are total 5 type of kyc

kyc = ['self','shg','mfg','jlg','or'];

if it find only one type of kyc like for example 'shg' then it return only

--  ---------- ---------- 
-- | kyc_type | count(*) |
--  ---------- ---------- 
-- | shg      | 2        |
--  ---------- ----------

this but I want others to return 0 if not exits how can I do it?

CodePudding user response：

You can perform a sub-select. First group by kyc_type and then for each type you perform a count.

select a.kyc_type,
       (
           select count(*)
           from kyc_table x
           where x.kyc_type = a.kyc_type and YEAR(created_at) = 2020
       ) as count
from kyc_table a
group by(kyc_type);