Count Returning blank instead of 0

You cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source.

If you want an output of 0 from the SUM and COUNT functions, then you should not use GROUP BY.

The reason is that when you have no records, the GROUP BY clause has nothing to group by, and then is not able to give you any output.

For example:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable

will return one record with the value '0', where as:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable
GROUP BY [Dummy]

will return no records.

I would imagine you need to change your joins from INNER to OUTER to ensure rows are returned even when there is no corresponding record in tbl_PawnItem -

SELECT 
    'Expired Item -'+ DateName(mm,DATEADD(MM,4,AE.fld_LoanDate)) as [Month]
    ,COUNT(PIT.fld_PawnItemID)'COUNT'
    ,SUM (PIT.fld_KaratGram)'GRAMS'
    ,SUM (PH.fld_PrincipalAmt)'PRINCIPAL'
FROM  #AllExpired AE
    LEFT JOIN Transactions.tbl_PawnItem PIT
        ON AE.fld_PawnMainID=PIT.fld_PawnMainID
    LEFT JOIN Transactions.tbl_PawnHisto PH
        ON AE.fld_PawnMainID=PH.fld_PawnMainID
GROUP BY DATENAME(MM,(DATEADD(MM,4,AE.fld_LoanDate)))