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.

ORDER BY Query
     SELECT expressions  
     FROM tables  
     [WHERE conditions]  
     ORDER BY expression [ ASC | DESC ];

Parameters:

ParametersDescription
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.

 

Example
     SELECT * FROM contacts  
     WHERE lastname = 'Yakkiparamban'  
     ORDER BY lastname ASC;

 

Output

MySQL Order by & Group by

 

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.

 

GROUP BY Query
     SELECT expression1, expression2, ... expression_n,   
     aggregate_function (expression)  
     FROM tables  
     [WHERE conditions]  
     GROUP BY expression1, expression2, ... expression_n;

 

Parameters:

ParametersDescription
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.

Query
     SELECT lastname FROM contacts ;

The output will be:

Output

MySQL Order by & Group by

 

To get the unique values for lastnames, we can use the following query.

Sample code
     SELECT lastname FROM contacts 
     GROUP BY lastname;

 

Output

MySQL Order by & Group by

 

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.

Query
     SELECT lastname, COUNT(*)  
     FROM   contacts   
     GROUP BY lastname;

 

Output

MySQL Order by & Group by

 

Anwar Yakkiparamban

Anwar Yakkiparamban is the founder of Lauyou Learning. Prior to Lauyou learning, Anwar worked at ARD Engineering & Development, Qatar. He holds bachelor degree in Electronics and Communication Engineering from Govt. Engineering College Idukki.

You may also like...