Page 1 of 1

SELECT filling in values it shouldn't

Posted: Fri Sep 27, 2002 10:04 pm
by calebsg
This is my SELECT statement:

Code: Select all

SELECT Accounts.AccountName, Sum(MEtable.MEGoogle) AS Google, Sum(MEtable.MEOverture) AS Overture, Sum(SOtable.SaleAmount) AS SumOfSaleAmount
FROM (Accounts LEFT JOIN MEtable ON Accounts.AccountCode = MEtable.MEAccountID) LEFT JOIN SOtable ON Accounts.AccountCode = SOtable.AccountCode
GROUP BY Accounts.AccountName;
The purpose of this query is to show my online marketing expenses and revenue for the 23 account categories in the Accounts table. The point being that I can get some feeling of what my ROI is. However, the query returns the wrong sum values although it does return exactly 23 rows. It seems that if there is a value in more than one column (other than AccountName column) it doubles up the addition.

Say I had an account "Printers", with $100 in Google Expenses and $200 in revenue in my data tables. It would show $200 in Google Expense and $400 in revenue in my query results. Why is this?

If I haven't made myself clear, please ask for clarification! THanks,

Caleb, at his wits' end.

Posted: Fri Sep 27, 2002 10:50 pm
by mydimension
have a look at this: http://www.mysql.com/doc/en/Group_by_functions.html
in reference to the Group By clause:
Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results.