在SQL中使用COUNT函数 [英] Using the COUNT function in SQL

问题描述

首先，这是作业的一部分.

First and Foremost, this is part of an assignment.

我正在尝试使用COUNT函数作为有关Northwind数据库的查询的一部分.该查询应返回CustomerID，CompanyName和每个客户的订单数.

I am trying to use the COUNT function as part of a query in relation to the Northwind database. The query should return the CustomerID, CompanyName, and the number of of orders placed for each respective customer.

当然，前两个部分很简单，但是我无法使COUNT函数正常工作.到目前为止，我的查询是:

Of course the first two parts are easy, but I can't get the COUNT function to work properly. The query I have so far is:

SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, COUNT(Customers.CustomerID)
FROM Orders, Customers
WHERE Customers.CustomerID = Orders.CustomerID;

以这种方式使用COUNT的正确语法是什么?看起来像:

What would be the correct syntax to use COUNT in that fashion? It would look like:

CompanyID | CompanyName | # of orders
    1     | Company A   | 4
    2     | Company B   | 3
    3     | Company C   | 5

到目前为止，所有示例本身都是使用COUNT函数，而不是更复杂的查询的一部分.

All examples thus far have been using the COUNT function by itself, and not part of a more complex query.

推荐答案

您需要一个group by子句，该子句可让您将结果分成组，并执行聚合函数(在这种情况下为count )，按组:

You need a group by clause, which will allow you to split your result in to groups, and perform the aggregate function (count, in this case), per group:

SELECT   Customers.CustomerID, Customers.CompanyName, COUNT(*)
FROM     Orders, Customers
WHERE    Customers.CustomerID = Orders.CustomerID;
GROUP BY Customers.CustomerID, Customers.CompanyName

注意:尽管这不是问题的一部分，但建议使用显式的join而不是您所使用的不推荐使用的隐式联接语法.在这种情况下，查询将类似于:

Note: Although this is not part of the question, it's recommended to use explicit joins instead of the deprecated implicit join syntax you're using. In this case, the query would look like:

SELECT   Customers.CustomerID, Customers.CompanyName, COUNT(*)
FROM     Orders
JOIN     Customers ON Customers.CustomerID = Orders.CustomerID;
GROUP BY Customers.CustomerID, Customers.CompanyName

这篇关于在SQL中使用COUNT函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！