Why wouldn't this code work? AVG()

Q&A DQ Courses
, ,
#1

Screen Link:
https://app.dataquest.io/c/43/m/255/subqueries/7/practice-integrating-a-subquery-with-the-outer-query

My Code: 

SELECT Major, Major_category, CAST(Sample_size AS FLOAT) / Total AS ratio
  FROM recent_grads
 WHERE ratio > AVG(Cast(Sample_size AS FLOAT) / Total)

What I expected to happen:
I expected it to process the comparison between ratio and the average ratio between the Sample_size and Total

What actually happened:

(sqlite3.OperationalError) misuse of aggregate function AVG()
[SQL: SELECT Major, Major_category, CAST(Sample_size AS FLOAT) / Total AS ratio   FROM recent_grads  WHERE ratio > AVG(Cast(Sample_size AS FLOAT) / Total)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

I would like to understand why isn’t SQLite doing the calculation that I indicated? The formula inside AVG()is selected, therefore SQL knows where to select the data from.

#2

The error you’re getting was discussed on the first screen of this lesson: Learn data science with Python and R projects

Although it doesn’t go in to much detail, the reason for the error is because we cannot use an aggregate function in a WHERE clause. However, we can use them in a HAVING clause! Short of the long here is that WHERE reads data “row by row” and checks the condition as it goes along. It does not have access to the entire column like HAVING does.

Check out this Stack Overflow post on the subject for more info.

1 Like