Thodoris Thodoris - 2 years ago 84
SQL Question

query about avg's max

I have this query: Find the branch and branche's city with the biggest avg amounts for product 'beta600' between 2010-2014.

I execute this code but I get no results. It takes about 10 min to execute and it gives null. Can u help?

SELECT DISTINCT branch_code,branch_city, AVG(trn_amount)) AS max_avg
FROM branches,transactions,products,accounts
WHERE branches.branch_code = accounts.branch_code AND
products.product_code = accounts.product_code AND
accounts.account_id = transactions.account_id AND
product_name = 'Beta600' AND
trn_date > '31/12/2009' AND trn_date < '1/1/2015'
GROUP BY branch_code,branch_city
ORDER BY max_avg
LIMIT 1;

Answer Source

Your approach seems reasonable, but the query could use some work. Here are recommendations:

  • Use explicit JOIN syntax. Simple rule: Never use commas in the FROM clause. *Alwaysuse explicitJOIN` syntax.
  • Use table aliases. They make the query easier to write and to read.
  • Use standard date formats -- YYYY-MM-DD.

The resulting query:

SELECT b.branch_code, b.branch_city, AVG(t.trn_amount)) AS max_avg
FROM branches b JOIN
     accounts a
     ON b.branch_code = a.branch_code JOIN
     transactions t
     ON a.account_id = t.account_id JOIN
     products p
     ON p.product_code = a.product_code
WHERE p.product_name = 'beta600' AND
      t.trn_date > '2009-12-31' AND t.trn_date < '2015-01-01'
GROUP BY branch_code, branch_city
ORDER BY max_avg
LIMIT 1;

If your query is returning no results, then the WHERE clause is filtering everything out. Perhaps the date format or casing of the product name is causing the problem.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download