CODEWITHSUNDEEP
mysqlsql
coder
coder

Reputation: 299

SQL / MySQL: Sum all amounts for respective month

I've got a table in my database which looks like this:

payment_id          customer_id         amount          payment_date            
1                   32                  20.00           2005-01-25 11:30:37
2                   32                  10.00           2005-01-26 11:30:37
3                   11                  25.00           2005-03-25 11:30:37

Now I want to sum all amounts a customer (customer_id) made in the respective month. I need a query that looks which month exists and which customers have an entry for this month.

The result should look like this:

customer_id         month           amount
32                  01              30.00
11                  03              25

I tried this:

SELECT DISTINCT month(payment_date) AS month, customer_id, sum(amount) AS amount
FROM table

But it just sums all amount values of the whole table.

Upvotes: 1

Views: 212

Answers (1)

fthiella
fthiella

Reputation: 49049

You have to use a GROUP BY query:

SELECT
  customer_id,
  month(payment_date) as month,
  sum(amount) as total_amount
FROM
  tablename
GROUP BY
  customer_id,
  month(payment_date)

Upvotes: 2

Related Questions