I have one table i need to just select for one month data till the sum of cost filed 5000.

select sum(cost) as R3c, min(id_r), max(id_r) from pro where
dt >= '2010-11-01 00:00:00' and dt <= '2010-11-31 23:59:59'
and sum(cost) <= 5000;

It give error Invalid Group By function.

Please smuggest how i can achieve this.

thank you

Have a look at SELECT .. GROUP BY and HAVING. For example

SELECT deptid, SUM(salary)
FROM depts
GROUP BY deptid
HAVING SUM(salary) > 1000;

After re-reading your post I am not sure whether you are looking to SUM(cost) where cost <= 5000?

I am trying very simple query. I need to keep sum(cost) <= 5000 for a month data in one table and delete rest records. Can you please give a simple sql for this.

I am not following what you are looking for? Can you provide a small example of what you are looking to select? I think what you are looking for is a runtime aggregator so that once the costs exceed 5000 you stop. Is that what you are looking for?

Hi,
I have one reseller he need to adjust the sum of cost 5000 only. now his account have more than 30,000 he ask us to delete the records so the sum should come only 5000.

select sum(cost) as TotalMonth_cost from tr_table where p_dt >= '2010-09-01 00:00:00' and p_dt <= '2010-09-31 23:59:59'

if i run this the total TotalMonth_cost will be 30,000, Now i need to delete some rows to adjust TotalMonth_cost only 5000.

This is what i am looking.

BR,

Try something like this:

mysql> set @total := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, salary, @total:=salary+@total from info;
+----+--------+-----------------------+
| id | salary | @total:=salary+@total |
+----+--------+-----------------------+
| 1 | 1 | 1 |
| 2 | 3 | 4 |
| 3 | 3 | 7 |
| 4 | 8 | 15 |
+----+--------+-----------------------+
4 rows in set (0.00 sec)

mysql> select id, salary, total from (select id, salary, @total:=salary+@total as total from info) a where a.total < 10;
+----+--------+-------+
| id | salary | total |
+----+--------+-------+
| 1 | 1 | 1 |
| 2 | 3 | 4 |
| 3 | 3 | 7 |
+----+--------+-------+
3 rows in set (0.01 sec)