Ola Natvig wrote > > Hi > > I have two tables in a MaxDB database, one is a account table, and the > other > table is a transactions table. I want to fetch the accounts that has a > balance greater than the average balance of all accounts. > > The tables looks like this. > > -------------- ---------------- > | ACCOUNTS | | TRANSACTIONS | > -------------- ---------------- > | ID | | ID | > | OWNER | | ACCOUNT_ID | > -------------- | AMOUNT | > ---------------- > > There are a foreign-key between ACCOUNT_ID and ACCOUNTS.ID. > > Is there a simple way to do this. > I've tried this query, but I think grouping functions are forbidden in > the where clause of the query. > > SELECT > OWNER, > SUM(AMOUNT) > FROM > ACCOUNTS > LEFT JOIN > TRANSACTIONS > ON ACCOUNTS.ID = ACCOUNT_ID > WHERE > SUM(AMOUNT) > > ( > SELECT > SUM(AMOUNT) / COUNT(ACCOUNT_ID) > FROM > INFSENSE_TEST_TRANSACTIONS > ) > GROUP BY OWNER > > - > Ola Natvig >
Change your WHERE to a HAVING And your select should do Elke SAP Labs Berlin > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
