SQL GROUP BY vs AVG (Problem 25 of SQL Practice Problems Book)

Question

The question is taken from problem 25 of SQL Practice Problems Book... [Problem 25][1] [1]: https://i.stack.imgur.com/fmJjs.jpg

25. High freight charges

Some of the countries we ship to have very high freight charges. We'd like to investigate some more shipping options for our customers, to be able to offer them lower freight charges. Return the three ship countries with the highest average freight overall, in descending order by average freight.

My first intuition was to run the following query:

SELECT shipcountry, freight
from orders
GROUP BY shipcountry
ORDER BY freight DESC
LIMIT 3;

which is apparently not the correct way to do it. The query below uses avg aggregation function, and is the correct way to query. I wanted to know why we need to run AVG function when we have the GROUP BY?

SELECT shipcountry, avg(freight) as mean_freight
FROM orders
GROUP BY shipcountry
ORDER BY mean_freight DESC
LIMIT 3;

Answer 1

The question asks about average freight cost per country; that strongly implies you'll need to use the AVG function (unless you like making extra work for yourself and you decide that using SUM and COUNT is more interesting in some way).

You can't avoid using aggregates somewhere along the line since the query is about aggregate values.