Not totaling individually, GROUP BY

[jonesrge]

Hey there,

Im having a problem with this query, I have one table that holds all user records, then I have other tables that have a log of all deposits they made on a website in a month. I want the query to total these deposits and return the total for each month next to the USERS ID. Now this works fine when I do it for one month, but if I had other month tables, the totals go all over the place,

Here is the query...

SELECT Deposit_Users.USER_ID,
Sum(Sept1.Total) AS Sept,
Sum(Aug1.Total) AS Aug
FROM (Deposit_Users LEFT JOIN Sept1 ON Deposit_Users.USER_ID = Sept1.[User Id]) LEFT JOIN Aug1 ON Deposit_Users.USER_ID = Aug1.[User Id]
GROUP BY USER_ID;


I need to make the query ONLY return the sum of all deposits on an individual month basis, it seems to be totaling everything or something for each column.

Any help on this would be much appreciated.

Thanks.

 

[Brianwarnock]

Isn't it just a case of grouping by month swell as user.

Brian

 

[plog]

>> I have other tables that have a log of all deposits they made on a website in a month <<

There's your first problem, unnormalized data. You should only have 1 table of deposits (actually if you have debit data as well it should be in that one table as well).

The second problem, and the one you would avoid altogether with properly normalized data, is that you need to SUM your monthly data prior to joining it to your Users table. You would need to first create sub-queries for all the months which would sum up the data in each table by user, then you LEFT JOIN that sub-query to the User table.

Again, normalization is the easier path, but it can be done with structure you have.