Sum statement has no records

[imtmub]

I have two tables. One is glaccounts and another is ITR(Inventory transaction)
Glaccounts contains the field glaccount.
glaccount are 121101, 121102, 121103,.......121110

ITR Table has information about the inventory transaction. ITR table also have itr_glacctnbr that link with glaccounts table.

I want to calculate amount i spend in a month respective accounts.

Here my query

Select dbo.Glaccounts.GlAccount,SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt)as Monthtotal
FROM dbo.ITR INNER JOIN
dbo.Glaccounts ON dbo.ITR.ITR_GLAcctNbr = dbo.Glaccounts.GlAccount
where itr_transdate between '2007-12-01' and '2007-12-31'
group by dbo.Glaccounts.GlAccount

This query display the result.
Glaccount Monthtotal
121101 -51794.238300000005
121103 -44021.349499999997
121104 -37982.490399999922
121105 -500813.61599999992
121107 -2700.0
121108 -671744.63323501509


For some GLaccount i dont have records for that month. If no records i need to display 0 .

I want result like this

Glaccount Monthtotal
121101 -51794.238300000005
121102 0.0
121103 -44021.349499999997
121104 -37982.490399999922
121105 -500813.61599999992
121106 0.0
121107 -2700.0
121108 -671744.63323501509
121109 0.0
121110 0.0

Please tell me the query to get this result

Thanks

[Delerna]

LEFT JOIN the table that dosn't have records for every month to the table that does have records for every month. then you can use isnull(fieldname,0) to return a zero where the months data is missing

[imtmub]

LEFT JOIN the table that dosn't have records for every month to the table that does have records for every month. then you can use isnull(fieldname,0) to return a zero where the months data is missing

Still I am getting the same result.

I have modified the query

SELECT dbo.Glaccounts.GlAccount,
isnull(SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt),0.00) AS Monthtotal
FROM dbo.ITR LEFT JOIN
dbo.Glaccounts ON dbo.ITR.ITR_GLAcctNbr = dbo.Glaccounts.GlAccount
WHERE (dbo.ITR.ITR_TransDate BETWEEN '2007-12-01' AND '2007-12-31')
GROUP BY dbo.Glaccounts.GlAccount

[deepuv04]

Still I am getting the same result.

I have modified the query

SELECT dbo.Glaccounts.GlAccount,
isnull(SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt),0.00) AS Monthtotal
FROM dbo.ITR LEFT JOIN
dbo.Glaccounts ON dbo.ITR.ITR_GLAcctNbr = dbo.Glaccounts.GlAccount
WHERE (dbo.ITR.ITR_TransDate BETWEEN '2007-12-01' AND '2007-12-31')
GROUP BY dbo.Glaccounts.GlAccount

USE THE FOLLOLWING

SELECT dbo.Glaccounts.GlAccount,
isnull(SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt),0.00) AS Monthtotal
FROM dbo.ITR RIGHT OUTER JOIN
dbo.Glaccounts ON dbo.ITR.ITR_GLAcctNbr = dbo.Glaccounts.GlAccount
WHERE (dbo.ITR.ITR_TransDate BETWEEN '2007-12-01' AND '2007-12-31')
GROUP BY dbo.Glaccounts.GlAccount