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

Question

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?

Answer 1

SELECT AVG(`bracket`) FROM `BRACKETS` GROUP BY `id`

Answer 2

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

Answer 3

What you want to do is mathmatically incorrect. You can't average tax rates that way. You need to know how many people are at each rate to get the average tax rate. This calculation would give a value of 6.67 (rounded and assuming you stored the tax rates in a decimal field and not an integer field (and of course you would never do a calculation of any sort on a float!)).

however assume 10,000,000 people are intax rate A, 1,000,000 people are in tax rate B and 100 people are in tax rate c. Now the real average tax rate is 5.09%.