Search code examples
phpsqlmysqlrecordset

How to get average tax rates with SQL from a one-to-many relationship

I have a database containing two tables - one, states, contains info about each state's income taxes, and the second, brackets, contains the tax brackets for each state connected to States by a numeric key.

I would like to use SQL to retrieve each state and the AVERAGE tax bracket amount for each state to output in a recordset. For example:

STATES

id name
1  Alabama

BRACKETS
id bracket amount
1  5%      1000
1  7%      2000
1  8%      10000

How can I do this with the least amount of SQL calls?

Solution 
  • SELECT s.name,Avg(b.bracket) as AverageTax FROM STATES s INNER JOIN BRACKETS b 
ON s.numerickey=b.numerickey
GROUP BY s.id,s.name,b.bracket