我的数据库中有一个表,看起来像这样:
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
现在,我想将客户(customer_id)在相应月份的所有金额相加。我需要一个查询,看看哪个月存在,哪些客户有这个月的条目。
结果应该是这样的:
customer_id month amount
32 01 30.00
11 03 25
我试过这个:
SELECT DISTINCT month(payment_date) AS month, customer_id, sum(amount) AS amount
FROM table
但它只是将整个表格的所有金额值相加。
您必须使用GROUP BY查询:
SELECT
customer_id,
month(payment_date) as month,
sum(amount) as total_amount
FROM
tablename
GROUP BY
customer_id,
month(payment_date)