What am I not doing to return 16 states in an alias field?

Question

--Write a query that returns a field called below_average_states_y2000

--that lists all states

--with an avg_math_4_score less than the average over all states

--in the year 2000

I've rewritten this many ways and have tried subquerying but I'm too tired to keep trying to get this to run right...out of time...exam in the am.

SELECT AVG(avg_math_4_score) AS avg_4, state AS below_average_states_y2000

FROM naep

WHERE year = '2000'

GROUP BY state

HAVING avg_math_4_score < AVG(avg_math_4_score) AS avg_4;

Answer 1

Hello,

Using the information (field names and tables) provided in your question, I recommend writing a two queries.

First query returns the average score for all states:

SELECT AVG(avg_math_4_score) AS avg_math_4_score

FROM naep

WHERE year = '2000'

The second query returns states that are less than the average score using the first query as a nested Select:

SELECT state AS below_average_states_y2000, AVG(avg_math_4_score)AS state_avg_math_4_score

FROM naep

WHERE year = '2000'

GROUP BY state

HAVING AVG(avg_math_4_score) < (SELECT AVG(avg_math_4_score) AS avg_math_4_score

FROM naep

WHERE year = '2000')