In the previous chapter we’ve looked into how to use aggregate functions on SQL. This time We’ll dive into sorting and grouping.
Order by
Let’s say you want to display list of people order by their birthday, we can use ORDER BY.
SELECT *
FROM people
WHERE birth_country = 'France'
ORDER BY birt_date
LIMIT 5;id | first_name | last_name | birt_date | gender | birth_country | asset-----+------------+-----------+------------+--------+---------------+-------726 | Lola | Broader | 1980-09-30 | Female | France | 20291822 | Alain | Hauxley | 1981-02-05 | Female | France | 20532139 | Leif | Bumpas | 1982-01-10 | Female | France | 66854646 | Zebulen | Shine | 1982-01-20 | Male | France | 99103756 | Garrett | Aronovich | 1983-02-21 | Female | France | 76229(5 rows)
As you can see, by default ORDER BY will sort in ascending order. If you want to sort in descending order, you can useDESC.
SELECT *
FROM people
WHERE birth_country = 'France'
ORDER BY birt_date DESC
LIMIT 5;id | first_name | last_name | birt_date | gender | birth_country | asset-----+------------+-------------+------------+--------+---------------+-------48 | Lynnet | Gott | 2020-10-26 | Male | France | 96272802 | Dedie | Cluer | 2016-09-25 | Female | France | 2450731 | Reyna | Fuchs | 2016-01-09 | Female | France | 65351581 | Gaby | Kondratenko | 2015-10-04 | Male | France | 83756432 | Baldwin | Padginton | 2013-12-02 | Female | France | 12338(5 rows)
You can also sort by multiple columns at once. Try this on your own.
SELECT first_name, asset
FROM people
ORDER BY first_name, asset;Group by
Let’s say if you want your result grouped by gender or country, how should this be done in SQL. GROUP BY allows you to group results by one or more columns:
SELECT birth_country, gender, AVG(asset) AS average_asset
FROM people
GROUP BY birth_country, gender
ORDER BY birth_country, gender;birth_country | gender | average_asset----------------------------------+--------+------------------------Afghanistan | Female | 43737.250000000000Afghanistan | Male | 64177.000000000000Albania | Female | 32219.000000000000Albania | Male | 80905.666666666667Angola | Male | 97302.000000000000Argentina | Female | 54449.000000000000Argentina | Male | 49176.111111111111Armenia | Female | 27877.000000000000Armenia | Male | 54431.000000000000Austria | Female | 20271.000000000000...
What you can see here is birth_country, gender and average asset were selected, displayed in alphabetical order by country name first then Female (which comes first in alphabetical order) and/or Male.
Also from those results, we can see in some countries only single gender data exists, example Angola.
Having something?
Now you have list of countries with gender and average asset. But what if you want to have a list with average asset over 50,000? In previous chapter, we’ve learnt to use aggregate functions, so let’s try:
SELECT birth_country, gender, AVG(asset) AS average_asset FROM people GROUP BY birth_country, gender WHERE AVG(asset) > 50000 ORDER BY birth_country, gender;If you have run this, you’d get an error ERROR: syntax error at or near "WHERE". Aggregate functions can’t be used directly with WHERE, that’s why HAVING clause exists. Try this :
SELECT birth_country, gender, AVG(asset) AS average_asset FROM people GROUP BY birth_country, gender HAVING AVG(asset) > 50000 ORDER BY birth_country, gender;birth_country | gender | average_asset----------------------------------+--------+--------------------Afghanistan | Male | 64177.000000000000Albania | Male | 80905.666666666667Angola | Male | 97302.000000000000Argentina | Female | 54449.000000000000Armenia | Male | 54431.000000000000...
It works great! Try on your own with other values.
Now we’ve finished very basics of SQL statements, from next chapter we’ll be looking into more advanced technique such as JOIN, UNION, INTERSECT, EXCEPT and etc…
