sql avg和count_SQL sum（），count（）和avg（）函数

sql avg和count

Hey, readers! In this article we will be focusing on some of the extensively used aggregate functions of SQL — sum(), count() and avg() function.

嘿，读者们！ 在本文中，我们将重点介绍一些广泛使用的SQL聚合函数-sum（），count（）和avg（）函数 。

---

---

SQL sum（）函数 (SQL sum() function)

The SQL sum() function does the summation of all the values of a particular column of a respective table and returns the value obtained as the summation of it.

SQL sum() function对各个表的特定列的所有值进行求和，并返回获得的值作为其求和。

Syntax:

句法：

SELECT SUM(column) 
FROM Table
WHERE condition;

In the above syntax, the WHERE clause is used to act upon a condition provided ahead of it.

在以上语法中， WHERE clause用于对前面提供的条件进行操作。

Example 1:

范例1：

create table Info(id integer, Cost integer);
insert into Info(id, Cost) values(1, 100);
insert into Info(id, Cost) values(2, 50);
insert into Info(id, Cost) values(3, 65);
insert into Info(id, Cost) values(4, 97);
insert into Info(id, Cost) values(5, 12);
SELECT SUM(Cost)
FROM Info;

In the above example, we have created a Table ‘Info’ containing columns ‘id’ and ‘Cost’. Further, we have used SQL sum() function to depict the summation of all the data values present in the column ‘Cost’.

在上面的示例中，我们创建了一个包含列“ id”和“ Cost”的表“ Info”。 此外，我们使用SQL sum（）函数来描述列在“成本”列中的所有数据值的总和。

Output:

输出：

324

Example 2:

范例2：

create table Info(id integer, Cost integer);
insert into Info(id, Cost) values(1, 100);
insert into Info(id, Cost) values(2, 50);
insert into Info(id, Cost) values(3, 65.45);
insert into Info(id, Cost) values(4, 97);
insert into Info(id, Cost) values(5, 12);
SELECT SUM(Cost)
FROM Info
WHERE Cost>20;

In this example, SQL WHERE clause is used to select only those data values of the column ‘Cost’ that have values greater than 20. These selected data values are passed to the SQL sum() function for summation of the values.

在此示例中，SQL WHERE子句仅用于选择列“ Cost”的值大于20的那些数据值。这些选定的数据值将传递到SQL sum（）函数以求这些值的总和。

Output:

输出：

312

---

---

带有GROUP BY子句SQL sum（）函数 (SQL sum() function with GROUP BY clause)

The SQL GROUP BY clause is used along SELECT statement to group identical data values against some particular group values.

SQL GROUP BY clause与SELECT statement一起使用，可以将相同的数据值与某些特定的组值进行分组。

Syntax of SQL GROUP BY clause:

SQL GROUP BY子句的语法：

SELECT * from Table
GROUP BY Column;

Example:

例：

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");

SELECT city, SUM(Cost)
FROM Info
GROUP BY city;

In this example, the GROUP BY clause is been used to group the identical data values of column ‘City’ by groups.

在此示例中，GROUP BY子句用于按组对列“城市”的相同数据值进行分组。

Further, these set of data values are passed to SQL sum() function and it performs summation of elements of those groups separately.

此外，这些数据值集将传递给SQL sum（）函数，并分别执行这些组元素的求和。

Output:

输出：

Mumbai	97
Pune	165
Satara	50
USA	12

---

---

SQL count（）函数 (SQL count() function)

SQL count() function counts the total number of data values present in a particular column passed to it.

SQL count() function对传递给它的特定列中存在的数据值总数进行计数。

Syntax:

句法：

SELECT COUNT(column) 
FROM Table
WHERE condition;

Example 1:

范例1：

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");


SELECT count(id)
FROM Info;

In the above example, we have displayed a count of data values present in the column ‘id’ using SQL count() function.

在上面的示例中，我们使用SQL count（）函数显示了列“ id”中存在的数据值的计数。

Output:

输出：

5

Example 2:

范例2：

SELECT count(id)
FROM Info
WHERE city = "Pune";

Considering the Table of Example 1, we have used SQL count() function alongside WHERE clause to count only those data values that happen to belong to the City ‘Pune’.

考虑示例1的表，我们在WHERE子句旁边使用了SQL count（）函数，仅对那些恰好属于City'Pune'的数据值进行计数。

Output:

输出：

2

---

---

带有GROUP BY子句SQL count（）函数 (SQL count() function with GROUP BY clause)

SQL count() function can be clubbed alongside GROUP BY clause to get the count of data values against a particular group of data.

可以将SQL count（）函数与GROUP BY子句结合使用，以获取针对特定数据组的数据值计数。

Example:

例：

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");

SELECT city,count(id)
FROM Info
GROUP BY city;

In the above example, we have used SQL GROUP BY clause to group the data values by the column ‘city’. After which, we have used the SQL count() function to calculate the number of data values associated to that particular groups formed.

在上面的示例中，我们使用SQL GROUP BY子句按列“ city”对数据值进行分组。 之后，我们使用了SQL count（）函数来计算与所形成的特定组关联的数据值的数量。

Output:

输出：

Mumbai	1
Pune	2
Satara	1
USA	1

---

---

SQL avg（）函数 (SQL avg() function)

SQL avg() function estimates the average of data values of a particular column passed to it.

SQL avg() function估计传递给它的特定列的数据值的平均值。

Syntax:

句法：

SELECT AVG(column) 
FROM Table
WHERE condition;

Example 1:

范例1：

SELECT avg(Cost)
FROM Info;

Referring to the Table created in the above section, we have used SQL avg() function to calculate the average of data values of the column ‘Cost’.

参考上一节中创建的表，我们使用了SQL avg（）函数来计算“成本”列的数据值的平均值。

Output:

输出：

64.8000

Example 2:

范例2：

SELECT avg(Cost)
FROM Info
WHERE city = "Pune";

In this example, we have calculate the average value of the data elements which belong to the city ‘Pune’.

在此示例中，我们计算了属于城市“浦那”的数据元素的平均值。

Output:

输出：

82.5000

---

---

带有GROUP BY子句SQL avg（）函数 (SQL avg() function with GROUP BY clause)

SQL avg() function along with GROUP BY clause is used to calculate the average of data values against respective groups of data.

SQL avg（）函数与GROUP BY子句一起用于针对各个数据组计算数据值的平均值。

Example:

例：

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");


SELECT city,avg(Cost)
FROM Info
GROUP BY city;

We have grouped the data values along the column ‘city’ and then calculated the average value of the data belonging to identical city.

我们已将数据值沿“城市”列分组，然后计算了属于同一城市的数据的平均值。

Output:

输出：

Mumbai	97.0000
Pune	82.5000
Satara	50.0000
USA	12.0000

---

---

结论 (Conclusion)

By this, we have come to the end of this topic. I hope all the queries have been answered by the above content.

至此，我们到了本主题的结尾。 希望以上内容能回答所有疑问。

Please feel free to comment below in case you come across a doubt.

如果您有任何疑问，请随时在下面发表评论。

---

---

参考资料 (References)

• SQL avg() with GROUP BY clause — StackOverFlow
  带有GROUP BY子句SQL avg（）— StackOverFlow
• SQL Aggregate functions — JournalDev
  SQL聚合函数— JournalDev

翻译自: https://www.journaldev.com/40669/sql-sum-avg-count

sql avg和count