CODEWITHSUNDEEP
mysqlsqldatabase
Joshua
Joshua

Reputation: 303

MYSQL - SUM of a column based on common value in other column

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

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

Expecting a result like:

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

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...

Upvotes: 3

Views: 2558

Answers (3)

Adam
Adam

Reputation: 159

Just by having a little Google search, I managed to find a page doing exactly what you're doing (I think). I have tailored the query below to fit your circumstance.

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

Link to Page with Tutorial on SQL Groups

Upvotes: 1

Pawel
Pawel

Reputation: 380

Select CustomerID, sum(PurchasePrice) as PurchaseTotal FROM sales GROUP BY CustomerID ORDER BY PurchaseTotal ASC;

Upvotes: 1

jbafford
jbafford

Reputation: 5668

You need to GROUP BY your customer id:

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

Upvotes: 9

Related Questions