sql中聚合函数和分组函数_SQL选择计数聚合函数-语法示例解释

sql中聚合函数和分组函数

The COUNT operator is usually used in combination with a GROUP BY clause. It is one of the SQL “aggregate” functions, which include AVG (average) and SUM.

COUNT运算符通常与GROUP BY子句结合使用。 它是SQL“聚合”功能之一，其中包括AVG(平均)和SUM。

This function will count the number of rows and return that count as a column in the result set.

此函数将对行数进行计数，并将该计数作为列返回到结果集中。

Here are examples of what you would use COUNT for:

以下是将COUNT用于以下用途的示例：

• Counting all rows in a table (no group by required)
  计算表中的所有行(不需要按组)
• Counting the totals of subsets of data (requires a Group By section of the statement)
  计算数据子集的总数(需要语句的“分组依据”部分)

For reference, here is the current data for all the rows in our example student database.

作为参考，这是示例学生数据库中所有行的当前数据。

select studentID, FullName, programOfStudy, sat_score from student; -- all records with fields of interest

This SQL statement provides a count of all rows. Note that you can give the resulting COUNT column a name using “AS”.

该SQL语句提供所有行的计数。 请注意，您可以使用“ AS”为所得的COUNT列命名。

select count(*) AS studentCount from student; -- count of all records

Here we get a count of students in each field of study.

在这里，我们得到了每个学习领域的学生人数。

select studentID, FullName, count(*) AS studentCount from the student table with a group by programOfStudy;

Here we get a count of students with the same SAT scores.

在这里，我们得到了具有相同SAT分数的学生人数。

select studentID, FullName, count(*) AS studentCount from the student table with a group by sat_score;

Here is an example using the campaign funds table. This is a sum total of the dollars in each transaction and the number of contributions for each political party during the 2016 US Presidential Campaign.

这是使用广告系列资金表的示例。 这是2016年美国总统大选期间每笔交易的总金额和每个政党的捐款额。

select Specific_Party, Election_Year, format(sum(Total_$),2) AS contribution$Total, count(*) AS numberOfContributions 
from combined_party_data
group by Specific_Party,Election_Year
having Election_Year = 2016;

As with all of these things there is much more to it, so please see the manual for your database manager and have fun trying different tests yourself.

关于所有这些事情，还有很多事情要做，所以请参阅数据库管理员手册，并尝试自己进行不同的测试，这很有趣。

翻译自: https://www.freecodecamp.org/news/sql-select-count-aggregate-function-explained-example/

sql中聚合函数和分组函数