SQL GROUP BY and ORDER BY

I am already passed the question but I still have confusion at topic 7.Casting question 7/9. Here is my question:

SELECT Major_category, CAST( SUM(Women) as Float )/CAST( SUM(Total) as Float) AS SW
FROM recent_grads LIMIT 5

I dont know why the query above only return 1 row x 2 columns.

Besides that, can you explain me why ORDER BY clause can’t be used before GROUP BY. ?

Well, it wouldn’t make much sense if you think of it.

Imagine you have a drawer full of socks of different colors and sizes. Say you want to pick out only the grey socks and order them by increasing size. Intuitively, it would make sense to first select all the grey socks and only then sort them by size, right?

1 Like

Because you haven’t used the GROUP BY statement, the query returns only the total SW (Share Women) for the whole table. This is because SUM is a summary statistic and is only computed once in this case.

When you add GROUP BY Major_category to your query, SUM is computed once for each Major_category, so there will be as many rows in the resulting table as there are distinct Major categories.

I hope this makes sense!

1 Like