MySql Group By and Order By

The previous Lesson of MySql Lessons Series was about MySql Count and in this lesson will learn about Group By and Order By in MySql.

MySql Group By

The basic structure of MySql Group By is given below:

SELECT ColumnNames,COUNT(ColumnName) FROM TableName
WHERE 1 GROUP BY ColumnName

MySql Group By is used to present the data in a table in the form of small groups. Consider the sales table shown in the image below:



If we want to show the data in the sales table in the form of customer name and total purchases the customer has made, then this is how we can do it by using MySql Group By:

SELECT customer_name, COUNT(salesid) As total_sales FROM sales
WHERE 1 GROUP BY customer_name

The result of the above query is shown in the image below:



You can see in the above image, the data of sales table is presented in the form of groups made on the basis of customer name. Similarly if we want to show the data in the sales table in the form of product name and the number of sales of the product in front of it, then this is how we can do it:

SELECT product_name, COUNT(salesid) As total_sales FROM sales
WHERE 1 GROUP BY product_name

The result of the above query is shown in the image below:



In the above query the groups are made on the basis of product name.

MySql Order By

The basic structure of MySql Order By is given below:

SELECT ColumnNames,COUNT(ColumnName) FROM TableName
WHERE 1 GROUP BY ColumnName ORDER BY ColumnName ASC

MySql Order By is used to sort the data of a table in ascending or descending order based on the column on which order by is applied. An example of MySql Order By Ascending is given below:

SELECT product_name, COUNT(salesid) As total_sales FROM sales
WHERE 1 GROUP BY product_name ORDER By total_sales ASC

The above query will sort the data in sales table in ascending order according to the total sales. The result of the above query is shown in the image below:



Similarly we can sort the data in descending order like this:

SELECT product_name, COUNT(salesid) As total_sales FROM sales
WHERE 1 GROUP BY product_name ORDER By total_sales DESC