5. Returning Multiple Results for Subqueries, This is the SQL Fundamentals portion of the Data Analyst for Python Path

Screen Link: (Learn data science with Python and R projects)

My Code:

SELECT Major_category, Major
    FROM recent_grads
   WHERE Major_category IN (SELECT Major_category
                            FROM recent_grads
                            GROUP BY Major_category
                            ORDER By SUM(TOTAL) DESC
                            LIMIT 3);

What I expected to happen: I expected the columns to return only 3 rows of data as dumb as that sounds when I look at the result

What actually happened: I think the Group BY function for Major Categories affected the LIMIT function, and therefore returned the top three performing major categories based on the ORDER BY function’s SUM(Total) DESC

SELECT Major_category, Major
    FROM recent_grads
   WHERE Major_category IN (SELECT Major_category
                            FROM recent_grads
                            GROUP BY Major_category
                            ORDER By SUM(TOTAL) DESC
                            LIMIT 3);

Would love it if someone explained how this syntax works in sequence. I know the order the functions run in, i think, with group by , then order by, then limit but what I don’t fully understand is the result of the query.

I’m very confused by your question. In it, you seem to display that you understand what’s going on, yet you’re asking a question. I’ll take a stab at it anyway.

The subquery. . .

SELECT Major_category
  FROM recent_grads
 GROUP BY Major_category
 ORDER By SUM(TOTAL) DESC
 LIMIT 3;

. . . returns. . .

Major_category
Business
Humanities & Liberal Arts
Education

So, your whole query is equivalent to

SELECT Major_category, Major
  FROM recent_grads
 WHERE Major_category IN ('Business', 'Humanities & Liberal Arts', 'Education');

I hope this helps.

1 Like

Hi, Bruno! Thank you! I just wanted to confirm that I understood, and see how to properly think of this query. Your response was helpful. I usually write my questions trying to summarize what I know, but I’m not sure if what I think i know is correct. Thanks again.

1 Like