Thodoris Thodoris - 6 months ago 8
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

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.