what is the difference between GROUP BY and ORDER BY in sql

Question

When do you use which in general? Examples are highly encouraged!

I am referring so MySql, but can't image concept being different on another DBMS

Answer 1

ORDER BY alters the order in which items are returned.

GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).

Answer 2

GROUP BY is used to group rows in a select, usually when aggregating rows (e.g. calculating totals, averages, etc. for a set of rows with the same values for some fields).

ORDER BY is used to order the rows resulted from a select statement.

Answer 3

The difference is exactly what the name implies: a group by performs a grouping operation, and an order by sorts.

If you do "SELECT * FROM Customers ORDER BY Name" then you get the result list sorted by the customers name.

If you do "SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive" you get a count of active and inactive customers. The group by aggregated the results based on the field you specified.

Answer 4

TABLE:
ID NAME
1  Peter
2  John
3  Greg
4  Peter

SELECT *
FROM TABLE
ORDER BY NAME

= 
3 Greg
2 John
1 Peter
4 Peter

SELECT Count(ID), NAME
FROM TABLE
GROUP BY NAME

= 
1 Greg
1 John 
2 Peter

SELECT NAME
FROM TABLE
GROUP BY NAME
HAVING Count(ID) > 1

=
Peter

Answer 5

They have totally different meaning and aren't really related at all.

ORDER BY allows you to sort the result set according to different criteria, such as first sort by name from a-z, the sort by the price highest to lowest.

(ORDER BY name, price DESC)

GROUP BY allows you to take your result set, group it into logical groups and then run aggregate queries on the groups. You could for instance select all employees, group them by their workplace location and calculate the average salary. This would give you the average salary of an employee at a given location in your database.

Answer 6

Some good examples there. Just like to add my own from webcheatsheet which gives good clear examples, as well as letting you execute your own SQL.

SQL Order By

SQL Group By