How to return 0 by default if column value not exists in table

Question

I have a table with columns amount and feehead

amount      feehead
4000.00     Examination_Fee
4000.00     Examination_Fee
0.00        Late_Fee_Fine
2500.00     Late_Fee_Fine
0.00        Re-Admission_Fee
0.00        Re-Admission_Fee
5500.00     Registration_Fee
5500.00     Registration_Fee
5500.00     Registration_Fee
5500.00     Registration_Fee
76500.00    Tution_Fee
84000.00    Tution_Fee

Now I want to sum by feehead in this query

select ISNULL(SUM(amount),0) as total
from tablename
where feehead in ('Admission_Fee','Examination_Fee','Financial_Assistance','Fine_Money','Graduation_Fee','Kinship','Laboratory_Fee','Library_Fee','Medical_Fee','Other','Re-Admission_Fee','Registration_Fee','Scholarship','Sports_Fee','Late_Fee_Fine','Tution_Fee')
group by feehead 

It sums all the rows in which feehead exists in table, now I want to return 0 if feehead not exists in table

How to do this?

Answer 1

One option enumerates the values as rows in a derived table, then brings the table with a left join:

select f.feehead, coalesce(t.sum_amount, 0) sum_amount
from (values 
    ('Admission_Fee'),
    ('Examination_Fee'),
    ('Financial_Assistance'),
    ...
) f(feehead)
left join (
    select feehead, sum(amount) sum_amount
    from mytable 
    group by feehead
) t
on t.feehead = f.feehead
group by f.feehead

You can also use a lateral join, or a subquery:

select 
    f.feehead,   
    (
        select coalesce(sum(t.amount), 0) 
        from mytable t
        where t.feehead = f.feehead
    ) sum_amount
from (values 
    ('Admission_Fee'),
    ('Examination_Fee'),
    ('Financial_Assistance'),
    ...
) f(feehead)