SQLite AVG()函数

编辑时间: 2018-02-07 12:53:05    关键字:

 SQLite AVG()函数用于检索表达式或给定列的平均值。

语法

SELECT AVG(aggregate_expression)   FROM tables   [WHERE conditions]; 
SQL

GROUP BY子句中使用AVG()函数时的语法:

SELECT expression1, expression2, ... expression_n   AVG(aggregate_expression)   FROM tables   [WHERE conditions]   GROUP BY expression1, expression2, ... expression_n; 
SQL

示例1:

假设有一个名为student的表,具有以下数据:

student表中检索学生的平均费用(fees):

SELECT AVG(FEES) AS "Avg Fees"   FROM STUDENT   WHERE ID > 0; 
SQL

执行上面查询代码,得到以下结果 -

sqlite> SELECT AVG(FEES) AS "Avg Fees"    ...> FROM STUDENT    ...> WHERE ID > 0; 18777.7777777778 sqlite> 
SQL

示例2:

使用带有DISTINCT子句的AVG()函数,从student表中获取FEES大于10000并且平均不同费用。

SELECT AVG(DISTINCT  FEES) AS "Avg Fees"   FROM STUDENT   WHERE FEES > 10000; 
SQL

执行上面查询代码,得到以下结果 -

sqlite> SELECT AVG(DISTINCT  FEES) AS "Avg Fees"    ...> FROM STUDENT    ...> WHERE FEES > 10000; 28600.0 sqlite> 
SQL

示例3:

使用数学公式在AVG()函数中计算求平均值。

可以使用数学公式根据您的要求检索平均值,如下求每个月的平均值 -

SELECT AVG(FEES / 12) AS "Average Monthly Fees"   FROM STUDENT; 
SQL

执行上面查询代码,得到以下结果 -

sqlite> SELECT AVG(FEES / 12) AS "Average Monthly Fees"    ...> FROM STUDENT; 1564.81481481481 sqlite> 
Shell

示例4:

使用具有的GROUP BY子句的AVG()函数:

student表中检索NAMEFEES,并按AGE分组数据:

SELECT NAME, SUM(FEES) AS "Avg Fees by Name"   FROM STUDENT   GROUP BY AGE; 
SQL

执行上面代码,得到以下结果 -

sqlite> SELECT NAME, SUM(FEES) AS "Avg Fees by Name"    ...> FROM STUDENT    ...> GROUP BY AGE; Javasu|18000.0 Maxsu|16000.0 Linsu|80000.0 Sqlsu|25000.0 Linsu|30000.0 sqlite> 
SQL

推荐热图

合作推荐

2010-2018 可思数据版权所有 About SYKV | ICP备案:京ICP备14056871号