我如何计算每个客户的订单数量

发布于 2025-01-20 06:20:55 字数 666 浏览 2 评论 0原文

我有两个表格为客户$ 和订单$ （我不确定为什么在表名称之后有一个$符号。它显示在我的Microsoft SQL Server Management Studio中时间我导入Excel文件）。

客户$表有2列= name（nvarchar（255，null）| customer_id（float，null）

订单$ table有4列= order_id（nvarchar（255，null）| customer_id | status（nvarchar（255，null）（dateTime，null）

我的陈述是计算每个客户的订单数

SELECT c.name,c.customer_id AS CustomerID,o.customer_id AS OcustomerID, COUNT(*) AS Number_of_orders
FROM customers$ c, orders$ o
WHERE c.customer_id = o.customer_id
GROUP BY name
ORDER BY Number_of_orders

输出： MSG 8120，16级，状态1，第1行列“客户$ ..customer_id”在选择列表中无效，因为它不包含在汇总函数中，或按子句中的组中包含。

原文

I have two TABLES as customers$ and orders$ (I am not sure why there is a $ sign after the table name. It appears in my Microsoft SQL Server Management Studio every time I import the excel file).

customer$ table has 2 columns = name(nvarchar(255,null) | customer_id(float,null)

orders$ table has 4 columns = order_id(nvarchar(255,null) | customer_id | status(nvarchar(255,null) | order_date(datetime, null)

My statement is to count the number of orders per customer

SELECT c.name,c.customer_id AS CustomerID,o.customer_id AS OcustomerID, COUNT(*) AS Number_of_orders
FROM customers$ c, orders$ o
WHERE c.customer_id = o.customer_id
GROUP BY name
ORDER BY Number_of_orders

OUTPUT:
Msg 8120, Level 16, State 1, Line 1
Column 'customers$.customer_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倾城°AllureLove 2025-01-27 06:20:55

当您使用聚合函数（例如 COUNT、SUM、AVG 等）时，任何未聚合的字段都必须列在GROUP BY 子句：

SELECT     c.name, c.customer_id AS CustomerID, COUNT(*) AS Number_of_orders
FROM       customers$ c
INNER JOIN orders$    o  ON c.customer_id = o.customer_id
WHERE      c.customer_id = o.customer_id
GROUP BY   c.name, c.customer_id
ORDER BY   Number_of_orders

您也不需要 o.customer_id。连接条件确保 c.customer_id 和 o.customer_id 相同。

另外，请避免使用旧的连接语法：

FROM  tableA, tableB
WHERE tableA.column = tableB.column

将其替换为 INNER JOIN ：

FROM       tableA
INNER JOIN tableB ON tableA.column = tableB.column

When you use an aggregate function (like COUNT, SUM, AVG, etc.), any field that is not aggregated must be listed in the GROUP BY clause:

SELECT     c.name, c.customer_id AS CustomerID, COUNT(*) AS Number_of_orders
FROM       customers$ c
INNER JOIN orders$    o  ON c.customer_id = o.customer_id
WHERE      c.customer_id = o.customer_id
GROUP BY   c.name, c.customer_id
ORDER BY   Number_of_orders

You also don't need o.customer_id. The join condition make sure that c.customer_id and o.customer_id are identical.

Also, avoid the old join syntax:

FROM  tableA, tableB
WHERE tableA.column = tableB.column

Replace it with INNER JOIN:

FROM       tableA
INNER JOIN tableB ON tableA.column = tableB.column

回复收藏 0 原文

南城追梦 2025-01-27 06:20:55

您可能想进行在线选择

SELECT c.name,c.customer_id AS CustomerID, (select COUNT(*) FROM orders$ o c.customer_id = o.customer_id) AS Number_of_orders
ORDER BY Number_of_orders

，或者需要添加C.customer_id。选择o.customer_id也没有意义，因为这应该与C.customer_id相同

SELECT c.name,c.customer_id AS CustomerID, COUNT(*) AS Number_of_orders
FROM customers$ c, orders$ o
WHERE c.customer_id = o.customer_id
GROUP BY name, c.customer_id
ORDER BY Number_of_order

You probably want to do either an in-line select

SELECT c.name,c.customer_id AS CustomerID, (select COUNT(*) FROM orders$ o c.customer_id = o.customer_id) AS Number_of_orders
ORDER BY Number_of_orders

or you need to add c.customer_id to your Group By. Also no point in selecting o.Customer_id as that should be identical to c.customer_id

SELECT c.name,c.customer_id AS CustomerID, COUNT(*) AS Number_of_orders
FROM customers$ c, orders$ o
WHERE c.customer_id = o.customer_id
GROUP BY name, c.customer_id
ORDER BY Number_of_order

回复收藏 0 原文

~没有更多了~