SELECT filling in values it shouldn't

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
calebsg
Forum Commoner
Posts: 28
Joined: Tue Jun 18, 2002 10:41 am

SELECT filling in values it shouldn't

Post 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.
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post 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.
Post Reply