D.Madu D.Madu - 7 months ago 11
SQL Question

MySQL - get the sum of the column value(s) based on same filed on the same table

I have a mysql table called tbl_collection and another table that has the name of the customers. I made an inner join. Join works well.

This is my tbl_collection table

+-----------+------------+---------------+
| customer | date | ach_val |
+-----------+------------+---------------+
| 30002 | 2012-02-02 | 200 |
| 30002 | 2012-02-05 | 250 |
| 30002 | 2012-02-06 | 122 |
| 30003 | 2012-02-03 | 500 |
| 30004 | 2012-02-04 | 425 |
| 30004 | 2012-02-06 | 225 |
| 30004 | 2012-02-10 | 300 |
+-----------+------------+---------------+


What I want is to get the sum of each customers ach_val for each month.

For example sum of ach_val of each customer, in 2012-02.

(ach_val) of 30002 = 200 + 250 + 122 = 572

(ach_val) of 30003 = 500 = 500

(ach_val) of 30004 = 425 + 225 + 300 = 950

This is what i was trying to do.

$r = mysql_query("select tbl_collection.customer, sum(tbl_collection.col_ach) as coll from tbl_collection inner join tbl_mas_customer on tbl_mas_customer.customer = tbl_collection.customer where rep = '503' and DATE_FORMAT(date, '%Y-%m') = '2012-02'");

Answer
select customer,sum(ach_val) from tbl_collection group by customer,month(date);
Comments