MYSQL - 基于其他列中的公共值的列的总和 [英] MYSQL - SUM of a column based on common value in other column

问题描述

我一直在制作 MySQL 查询来解决问题.我正在尝试遍历销售"列表，在其中尝试按累计总支出对列出的客户 ID 进行排序.

I'm stuck on crafting a MySQL query to solve a problem. I'm trying to iterate through a list of "sales" where I'm trying to sort the Customer IDs listed by their total accumulated spend.

|Customer ID| Purchase price|
10          |1000
10          |1010
20          |2111
42          |9954
10          |9871
42          |6121

如何遍历汇总客户 ID 相同的购买价格的表格?

How would I iterate through the table where I sum up purchase price where the customer ID is the same?

期望结果如下:

Customer ID|Purchase Total
10          |11881
20          |2111
42          |16075

我必须:选择客户 ID，sum(PurchasePrice) 作为 PurchaseTotal from sales where CustomerID=(select distinct(CustomerID) from sales) order by PurchaseTotal asc;但它不起作用，因为它不遍历 CustomerID，它只需要单个结果值...

I got to: select Customer ID, sum(PurchasePrice) as PurchaseTotal from sales where CustomerID=(select distinct(CustomerID) from sales) order by PurchaseTotal asc; But it's not working because it doesn't iterate through the CustomerIDs, it just wants the single result value...

推荐答案

您需要GROUP BY您的客户 ID:

You need to GROUP BY your customer id:

SELECT CustomerID, SUM(PurchasePrice) AS PurchaseTotal
FROM sales
GROUP BY CustomerID;

这篇关于MYSQL - 基于其他列中的公共值的列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！