Question
What’s the difference between GROUP BY and ORDER BY?
Answer
The GROUP BY statement is used when we want to group the results of a query by one or more columns. It’s often used in conjunction with aggregate functions like COUNT, SUM, and AVG.
The ORDER BY statement is used when we want to sort the results of a query in either ascending or descending order.
Let’s take a look at an example to help us solidify our understanding. Suppose we have the following table called people:
Say we wanted to find out how many people have the same age for every age listed in the table. We can use the GROUP BY statement along with the aggregate function COUNT to do this:
SELECT age, COUNT(id) FROM people GROUP BY age;
Here’s what we get when we run that query:
As you can see, we get all of the ages in the table, and we get a count of how many people are that age. If we then wanted to sort this table by oldest to youngest, we would use the ORDER BY statement:
SELECT age, COUNT(id) FROM people GROUP BY age ORDER BY age DESC;
Here’s the final result:
11 Likes
If you had three columns, date, status and risk… How could you GROUP BY status then ORDER BY risk, then by date?
One minor complication… there are three risk levels (High, Medium, Low. I would not want to sort alphabetically… I’d want High to be first, Medium second, and Low third.
Can this be done?
This is a really interesting question,
maybe @mtf or @factoradic could help lay out an example on how to do this?
I am just a beginner, but I believe you’ll have to choose only one column to ORDER BY.
I did see “Sub queries” mentioned before in the forum though, so it might be possible?
I’m especially interested in learning to order the risks level in a custom order.
I would just try to add a number to them like ‘1 High’, ‘2 Medium’ and ‘3 Low’ and do this:
SELECT status, risk, COUNT(*)
FROM your_table_name
GROUP BY status
ORDER BY risk;
But then, I’m sort of taking a shortcut around your question and avoiding research.
I’m also a beginner with SQL, so the only way that I could think of to solve this would be with an indirect method using a case statement. Basically, you would be creating a helper column utilizing a case statement where you would substitute numeric values for High, Medium and Low, which you could then order by from lowest to highest numeric value:
SELECT date, status, risk,
CASE
WHEN risk = ‘High’ THEN 1
WHEN risk = ‘Medium’ THEN 2
ELSE 3
END AS ‘risk_num’
FROM your_table_name
GROUP BY status
ORDER BY risk_num, date;
2 Likes