SQL Server 2014 – Show record even if zero (General Ledger reporting)

I am creating a General Ledger report for the finance team. I have a list of all transactions by calendar Month. Of course some GL codes are not used every month for whatever reason. I do have another table with a list of all active GL codes.

What I would like to do is display in the first column a list of all active GL codes, then beside it a SUM of all transactions in that month. If a GL codes has not transactions Id still like to display the code but with a zero value.

Ive tried left joining to each of the tables but I end up with each GL code having the same sum amount multiplies by the amount of codes, for example.

1. 3115 – Resourcing – -897483.18
2. 3110 – Maintenance – -897483.18
3. 3115 – other – -897483.18

Expected result

1. 3115 – Resourcing – -299161.06
2. 3110 – Maintenance – 0
3. 3115 – other – 0

Using SQL Server 2014, hoping the above is enough information. I’m sure I am missing something simple, thanks Legends..

Answer

You can just use a correlated subquery:

select gl.*,
       (select sum(t.amount)
        from transactions t
        where t.gl_code = gl.gl_code and
              t.transaction_date >= @date_start and
              t.transaction_date < @date_end
       ) as sum_amount
from gl;

@date_start and @date_end are just placeholders for whatever logic you use to define the month.