CODEWITHSUNDEEP
mysqlsql
user3755249
user3755249

Reputation: 9

Mysql - query including sum and inner join

we have the following table structure:

Accounts: id | name .....

transactions: 

account_id | order_year | amount .....

our database is already very big (>300.000) accounts and transactions > 1.000.000

in our accounts list we now need a filter to search for accounts that had in sum of the amount more than XXXXEUR within year YYYY.

we already tried with subselect(inner Join and sum but our queries were much too slow.

maybe anybody can giv us some hints about how to make this query regarding performance.

thanks Pete

thanks for your fast answeres....

now we also have contacts that are related to accounts as follows:

account: id

contact: id

account_contact: id | contact_id | account_id

we also need the requested feature for searching within contacts.

so search for contacts that have (in sum) in year XXX more than YYYY EUR transactions.

many thanks for any help

Upvotes: 0

Views: 84

Answers (2)

Joshua Huber
Joshua Huber

Reputation: 3533

So I worked up your relationship in SQL Fiddle http://sqlfiddle.com/#!2/3b45af/1/0 on MySQL 5.5, and populated it with approx. 250,000 accounts and 1,000,000 transactions. The analytic query only takes 1-2 seconds to run on the test data:

SELECT
   accounts.id
 , SUM(amount)
FROM accounts
JOIN transactions
  ON (accounts.id = transactions.account_id)
WHERE order_year = 2005
GROUP BY accounts.id
HAVING SUM(amount) > 1000000;

The crucial part for performance is to make sure you have the tables properly indexed. accounts.id you probably already have indexed implicitly because it should be a PK. The other index that helped was to have a non-unique index on transactions.order_year:

CREATE INDEX transaction_year ON transactions(order_year);

The efficiency of the transaction_year index will depend on how selective it is. If you have 20 years worth of transactions, then the index will help considerably. If you only have 1-2 years of transactions, the index might not help at all.

EDIT:

SELECT
   accounts.id
 , accounts.name
 , SUM(amount)
FROM accounts
JOIN transactions
  ON (accounts.id = transactions.account_id)
WHERE order_year = 2005
GROUP BY accounts.id, accounts.name
HAVING SUM(amount) > 1000000;

Upvotes: 0

georstef
georstef

Reputation: 1388

Try this:

select
  Accounts.id,
  sum(Transactions.amount) as TotalAmount
from
  Accounts
inner join
  Transactions on Transactions.account_id = Accounts.id
where
  order_year = YYYY
group by 
  Accounts.id
having
  sum(Transactions.amount) >= XXXX

Upvotes: 2

Related Questions