Great question, essentially you'll use ORDER BY to sort results, and GROUP BY to group calculations of an aggregate function based on a field value.
Let's start by digging into ORDER BY.
Imagine we have a list of people and their ages:
name | age
Billy | 12
Sarah | 24
Aaron | 14
David | 19
If we wanted to sort alphabetically by name, we'd use an ORDER BY function like so:
ORDER BY name
Keep in mind, we could also sort in numeric order as well (ex: the age field). ORDER BY is not limited to sorting words.
ORDER BY age
Full transparency, the best thing I can recommend for understanding GROUP BY statements is to read through our GROUP BY | BUS 104 - SQL Exam article. It breaks down the intricacies of GROUP BY very well.
For the sake of this forum answer though, lemme try to explain it in a quick example.
Imagine we had the following table of test scores between Test 1 and Test 2 for a given class:
test_num | score
Test 1 | 75
Test 1 | 81
Test 1 | 93
Test 2 | 63
Test 2 | 73
Test 2 | 68
We could calculate the average of all scores easily with the following in our SELECT statement:
However, if we wanted to calculate the average of scores for each of the tests, we'd need to include the test_num field in our SELECT statement. Otherwise, in our results we wouldn't be able to determine which average calculation belongs to each test.
SELECT test_num, AVG(score)
By doing this, we're essentially grouping the results of the aggregate AVG(score) function by the test_num values. We need to explicitly tell SQL that this is what we're doing with a GROUP BY statement, otherwise SQL will get confused why we have a field and an aggregate function in our SELECT statement and return an error.
GROUP BY test_num
The Golden Key of GROUP BY
If all of this GROUP BY stuff is still confusing you, just remember and apply the following statement as you're taking your exam. It is the golden key of knowing when and how to use a GROUP BY statement!
WHENEVER YOU HAVE FIELDS AND AGGREGATE FUNCTIONS IN YOUR SELECT STATEMENT, PLACE THE FIELDS IN A GROUP BY STATEMENT!