为了账号安全,请及时绑定邮箱和手机立即绑定

MySQL 聚合函数

标签:
MySQL

Summary: in this tutorial, you will learn how to use the MySQL aggregate functions including AVGCOUNTSUMMAXand MIN.

Introduction to MySQL aggregate functions

MySQL Aggregate Functions - Order Details

The data that you need is not always stored in the tables. However, you can get it by performing the calculations of the stored data when you select it. For example, you cannot get the total amount of each order by simply querying from the orderdetails table because the orderdetails table stores only quantity and price of each item. You have to select the quantity and price of item for each order and calculate the order’s total. To perform such calculations in a query, you use aggregate functions.

By definition, an aggregate function performs a calculation on a set of values and returns a single value.

MySQL provides many aggregate functions including AVGCOUNTSUMMIN MAX, etc. An aggregate function ignores NULL values when it performs calculation except for the COUNT function.

AVG function

The AVG function calculates the average value of a set of values. It ignores NULL values in the calculation.

AVG(expression)

You can use the AVG function to calculate the average buy price of all products in the products table by using the following query:

SELECT AVG(buyPrice) average_buy_price FROM products

The AVG function in detail.

COUNT function

The COUNT function returns the number of the rows in a table. For example, you can use the COUNT function to get the number of products in the products table as the following query:

SELECT COUNT(*) AS Total FROM products

The COUNT function has several forms such as COUNT(*) and COUNT(DISTINCT expression). For more information, check it out the COUNT function tutorial.

SUM function

The SUM function returns the sum of a set of values. The SUM function ignores NULL values. If no matching row found, the SUM function return NULL.

To get the total sales of each product, you can use the SUM function in conjunction with the GROUP BY clause as follows:

SELECT productCode,sum(priceEach * quantityOrdered) total FROM orderdetails GROUP by productCode

To see the result in more detail, you can join the orderdetails table to the products table as the following query:

SELECT P.productCode,        P.productName,        SUM(priceEach * quantityOrdered) total FROM orderdetails O INNER JOIN products  P ON O.productCode = P.productCode GROUP by productCode ORDER BY total

 

1

 

More information on the SUM function in detail.

MAX function

The MAX function returns the maximum value in a set of values.

 

https://img1.sycdn.imooc.com//5b607f8500014e0101940066.jpg

原文链接:http://outofmemory.cn/mysql/function/mysql-aggregate-functions


点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消