GROUP BY count also missing types (Maria DB)

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0