How to MySQL- ORDER BY & GROUP BY statements
In this guide, we learning how to use MySQL ORDER BY & GROUP BY statements. Before you continue its recommended to read previous MySQL guides.
MySQL ORDER BY
The ORDER BY keyword is used to sort the result-set by one or more columns. It sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ ASC | DESC ];
|expressions:||The columns that you want to retrieve.|
|tables:||It specifies the tables, from where you want to retrieve records. There must be at least one table listed in the FROM clause.|
|WHERE conditions:||It is optional. It is the conditions that must be fulfilled for the records to be selected.|
|ASC:||It is optional. It sorts the result set in ascending order by expression|
|DESC:||It is also optional. It sorts the result set in descending order by expression.|
SELECT * FROM contacts WHERE lastname = 'Yakkiparamban' ORDER BY lastname ASC;
MySQL GROUP BY
The GROUP BY clause is a SQL command that is used to group rows that have the same values. The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item.
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
|expression1, expression2, ... expression_n:||It specifies the expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.|
|aggregate_function||It specifies a function such as SUM, COUNT, MIN, MAX, or AVG etc. tables: It specifies the tables, from where you want to retrieve the records. There must be at least one table listed in the FROM clause.|
|WHERE conditions||It is optional. It specifies the conditions that must be fulfilled for the records to be selected.|
Grouping using a Single Column Example:
Let’s execute a simple query that returns all the lastname entries from the contacts table.
SELECT lastname FROM contacts ;
The output will be:
To get the unique values for lastnames, we can use the following query.
SELECT lastname FROM contacts GROUP BY lastname;
GROUP BY with an aggregate function:
Suppose we want the total number of contacts those have the same lastname. We can use the following query to do that.
SELECT lastname, COUNT(*) FROM contacts GROUP BY lastname;