SQL_Subqueries_ 4/8

Can someone explain what does below question mean. PETROLEUM ENGINEERING is coming on top where as sum(total) value is 2339 where as other Engineering has higher values, so how come its coming on top if sorted descending.

Write a query that returns the Major and Major_category columns for the rows where:

  • Major_category is one of the 5 highest group level sums for the Total column

Here’s what the first 3 rows of the final table should look like:

Major Major_category
PETROLEUM ENGINEERING Engineering
MINING AND MINERAL ENGINEERING Engineering
METALLURGICAL ENGINEERING Engineering

Isn’t that correct? Descending sort means descend from large to small --> top value is largest.
What i don’t understand is where as other Engineering has higher values
I was expecting whereas other Engineering have lower values

The first 3 rows were provided just to let you verify your output is correct. It does not mean those 3 rows individually have any particular order on any column value. The competition/comparison is between their Major_category Engineering vs other major categories like Business, Education, and not between themselves within the same Major_category. The comparison is AFTER groupby, these rows are grouped and have a summary statistic calculated first before comparison.

Note that the outer sql selected Major in addition to Major_category just for data understanding purposes. What if it selected all columns instead? Would that be even more confusing? My point is to focus on the Major_category column only. Because that is central to this analysis. The other columns are just shown to add more information.

When doing SQL, you can build up an analysis by selecting the minimal amount of columns first in the innermost nest. When you realize you need to select something in an outer level, then you add that column selection to the inner level so it’s available to the outer level