SELECT Major_category, Major
WHERE Major_category IN (SELECT Major_category
GROUP BY Major_category
ORDER BY SUM(TOTAL) DESC
I am struggling with the reason why ORDER BY clause does not require a subquery to be functional.
Does it work with a virtual column just like HAVING clause?
If so, is that mean that I can order the outcome by a range which is not even exist in the current table?
Welcome to the community! Firstly, WHERE does not need to have a subquery to be functional (i.e. SELECT * FROM <table_name> WHERE <condition> as described here). HAVING is used to perform aggregate functions that WHERE could not perform as described in the documentation. In addition, INNER JOIN and subqueries are generally interchangeable. Note that HAVING can also be used together with WHERE as a “second layer” filter as shown in the example below.
/* The following SQL statement lists if the employees "Davolio" or "Fuller" have
registered more than 25 orders:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
The reason why the ORDER BY can take an aggregation directly in my code is because I grouped the table first.
If I did not use the GROUP BY first, the aggregation will come up with a single value, which have no meaning to be ordered by.
Is that correct?
However, I still cannot completely understand why it is invalid to use aggregation in the WHERE clause.
Like the code below, we don’t need to GROUP BY first to calculate AVG(ShareWomen).
So why can’t I just apply the AVG(ShareWomen) with WHERE clause?
Is that merely because it is how the SQL syntax is designed?
WHERE ShareWomen > AVG(ShareWomen);
In the future, please also remember to provide a question link as per these guidelines to allow us as community learning assistants to better assist you.