Incorrect sum
Incorrect sum
Table t_expenses
01/01/2008 $100.00 Entertainment
01/03/2008 $20.00 Entertainment
02/03/2008 $500.00 Transportation
02/05/2008 $10.00 Other
03/06/2008 $100.00 Entertainment
05/09/2008 $60.00 Other
06/09/2008 $40.00 Other
I run the following query on "t_expenses":
SELECT year(dt.Date_Column) Yr, month(dt.Date_Column) Mth, sum(ent.Amount) FROM "t_expenses" dt LEFT JOIN "t_expenses" ent ON year(dt.Date_Column)=year(ent.Date_Column) and month(dt.Date_Column)=month(ent.Date_Column) where ent.Category='Other' group by year(dt.Date_Column), month(dt.Date_Column)
which results in the following query:
2008 2 20.0
2008 5 60.0
2008 6 40.0
The first row is twice the amount it should be, but all the other rows add up correctly. If I change the query category (to Entertainment or Transportation), it always doubles the first row.
What have I done wrong?