SQL Basics - Grouping Data

We previously learned now to use aggregate functions to rerieve the average of a column or of entries matching a specific condition filtered with WHERE. Now we will learn how to get all the averages of the table grouped by a column entry. For example, you are a teacher with 20 kids in your class. Each student took six exams and you want ot get each child's average. You can use AVG() and WHERE student_name='whatever', but you would have to do it 20 times for all your students. Here's where grouping comes into play.
SELECT AVG(test_score) FROM table_tests
GROUP BY student_name
Another example. Using our old database we'll get the sum of each order in a separate entry.
SELECT order_num, SUM(quantity*item_price) FROM OrderItems
GROUP BY order_num

How about if we want to get a list of all students with an average below 65. We would think to use the WHERE clause, but it won't work. WHERE filters rows only, not groups. For groups, we use the HAVING clause.
SELECT AVG(test_score) AS average
FROM table_tests
GROUP BY student_name
HAVING average <>
One more thing to note. If you were to have all your classes in one table and you wanted the average of all failing students from just your Grade 6 class, here's how you'll do it:
SELECT AVG(test_score) AS average
FROM table_tests
WHERE column_grade = 6
GROUP BY student_name
HAVING average <>
ORDER BY student_name
Notice I used ORDER BY to sort the data, because although GROUP BY groups the data by name, it will not necessarily be in alphabetical order. Note that the clauses must actually be in the above order. SELECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY