mysql aggregate function

First, the classification of aggregation functions

Count .count Count the number of rows: used to count the number of values in a table or a column

                          grammar:
  • Counts all rows, including null

all: count all non-null rows for the specified column

Ifnull(null,2) is not null, if the first value is null, then choose the second value

round(10/3,2) 2 is 10 divided by 3 the number of decimal places in the error

.sum Computes the total value of a column

.avg Calculates the average of a column

.max Computes the maximum value of a column

Compute the minimum value of a column

How many students are there in the class*/
select count(*) FROM student01;
/* Query students whose gender is male */
select COUNT(*) from student01 WHERE gender='male';
/* Query how many people have taken the mysql exam: remove non-null */
SELECT COUNT(ALL sqlScore)FROM student01;
/*shorthand*/
SELECT COUNT(sqlScore)FROM student01;
 /* Calculate the total value of a column */
SELECT SUM(sqlScore)FROM student01;
/* Calculate the average of a column */
select SUM(sqlScore)/COUNT(*)FROM student01;
/*IFNULL=non-null, if the first is null then select the second */
SELECT IFNULL(null,2);
/* Calculate column average */
SELECT AVG(IFNULL(sqlScore,0))AS "Grade Average" FROM student01;
/* Calculate the maximum value of the column */
SELECT MAX(sqlScore)FROM student01;
/* Calculate column minimum */
SELECT MIN(sqlScore)FROM student01;
/*round, save the number of decimal places */
SELECT ROUND(10/3,3);
/* Display the average grade with one decimal place */
SELECT ROUND(AVG(IFNULL(sqlScore,0)),1)AS "Grade Average" FROM student01;

Leave a Reply

Your email address will not be published.

en_USEnglish