Subquery question

Why does the code from Group Summary Statistic P4 works

SELECT Major_category, avg(Low_wage_jobs) / avg(Total) as share_low_wage
from recent_grads
group by Major_category having share_low_wage > 0.1

While the code in Subqueries P1 require nesting?

SELECT *, AVG(ShareWomen) from recent_grads
having ShareWomen > AVG(ShareWomen)

Thanks for the help!

Hey there!

I’m not sure which mission you’re referencing (links would be super helpful in the future), but, from what I can see, the subquery was necessary in the second code block because you’re trying to grab the average of ShareWomen prior to any filtering you’re doing in the main query.

1 Like

Attached is the link below, so basically subqueries is needed when the value you are using to filter requires calculation? (Which means not available directly from the table) Thanks for your help!

Why does the code from Group Summary Statistic P4 works

https://app.dataquest.io/m/255/subqueries

SELECT Major_category, avg(Low_wage_jobs) / avg(Total) as share_low_wage
from recent_grads
group by Major_category having share_low_wage > 0.1

While the code in Subqueries P1 require nesting?

https://app.dataquest.io/m/255/subqueries/4/returning-multiple-results-in-subqueries

SELECT *, AVG(ShareWomen) from recent_grads
having ShareWomen > AVG(ShareWomen)

Thanks for the help!

Hmm… I’m not sure I’m understanding — the queries you listed above have entirely different purposes. The reason you’d use a subquery is to generate a result that is independent from the main query, which is what the second code you’ve listed is doing.

SELECT *, AVG(ShareWomen) from recent_grads
having ShareWomen > AVG(ShareWomen)

One issue with this query here is that you’ve used a HAVING clause, even though there was no grouping. Have you tried replacing that with a WHERE instead?

Note this distinction between HAVING and WHERE:

‘WHERE’ clause is used to filter individual rows, before grouping/aggregation has taken place.

‘HAVING’ is used to filter groups, after grouping/aggregation has taken place.

1 Like