CYBER WEEK - EXTRA SAVINGS EVENT
TRY A FREE LESSON

How is an alias allowed in the WHERE clause now?

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

My Code:

SELECT Major,
       Major_category,
       CAST(Sample_size AS Float)/ CAST(Total AS Float) AS ratio
FROM recent_grads
HAVING ratio > (SELECT AVG(CAST(Sample_size AS Float)/CAST(Total AS Float)) AS avg_ratio
                FROM recent_grads
               )

What I expected to happen: I expected this to work with the HAVING clause based on the previous lessons with aliases not being used/supported in the WHERE clause

What actually happened: My code doesn’t work and the answer actually has the alias ‘ratio’ in the WHERE clause. I’m so lost now. It also says I need a GROUP BY clause to use Having

(sqlite3.OperationalError) a GROUP BY clause is required before HAVING
[SQL: SELECT Major,        Major_category,        CAST(Sample_size AS Float)/ CAST(Total AS Float) AS ratio FROM recent_grads HAVING ratio > (SELECT AVG(CAST(Sample_size AS Float)/CAST(Total AS                   Float)) AS avg_ratio                 FROM recent_grads                 GROUP BY Major_category                )]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Solution:

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

Hi @dharrisco13:

From Step 6 of the mission, you should already have the alias specified (i.e. you are working on the inner query) and then on Step 7 you will be working on the outer query.

Not creating the alias also works as shown below.

Yes you will need it if you are using HAVING. Why not use WHERE instead? You can think of HAVING as a second filter for those not captured by WHERE.

Thanks for your help! In step 6, we are working on Avg Ratio and not Ratio. I’m not really understanding how ratio is already declared.

Hi! Sometimes some SQL flavors don’t follow the standard for the sake of the optimization. And it seems that in SQLite it’s allowed to have alias declared in the SELECT in WHERE clause:
https://stackoverflow.com/questions/10923107/using-a-column-alias-in-sqlite3-where-clause

1 Like