MySQL – Sorting Operations and Aggregation Functions

Time:2019-9-10

Order by, sort the query results and place them at the end of the select clause

asc —> Ascending order (default)

desc —> Descending order

Single column sorting

/* Age in descending order*/
 select * from t_students order by age desc;

Aliases can’t be sorted by single quotation marks!!!

Multiple Column Sort

/* first, ID is sorted in ascending order, then age is sorted in descending order.*/
  select * from t_students order by id ,age desc;

MySQL - Sorting Operations and Aggregation Functions

Alias sorting of columns

select *,id+age as XX from t_students order by XX desc;

The order by clause is executed after the select clause.

Aggregation function

Acting on a set of data and returning a value to a set of data

Name Effect
count Number of records of statistical results
max Statistical Maximum
min Statistical Minimum
sum Summation of Statistical Computation
avg Statistical average
/* Find the average of a column*/
select avg(age) from tablename;
/* Find the total number of id = 2*/
select count(id) from tablename where id=2;
/* Seeking the highest, lowest and average prices of commodities*/
select max(saleprice),min(saleprice),avg(saleprice) from tablename;