I have an sql table trade with following data
id| value
1| 0.1
2| 0.5
3| 0.9
4| 0.3
How do I make an SQL query so that i get the count of entries limited to total value of 0.8 ascending by id in php. For example:-
COUNT id FROM trade WHERE SUM(value) > 0.8 by id ASC
Result should come as
3
You need to do cumulative judgment,due to your mysql version didn't support window function so the solution will be a little hard to read,becuase you need to write subquery instead of window function.
make the cumulative number by Id column, then get the MIN(ID) in subquery, when Id = 3 the value will greater than 0.8.
finally get the Id small and equal the MIN(ID) you will get your expect result.
CREATE TABLE trade(
id INT,
value FLOAT
);
INSERT INTO trade VALUES(1,0.1);
INSERT INTO trade VALUES(2,0.5);
INSERT INTO trade VALUES(3,0.9);
INSERT INTO trade VALUES(4,0.3);
Query 1:
SELECT COUNT(*)
FROM trade t1
WHERE t1.id <= (
SELECT MIN(ID) FROM (
SELECT ID,(
SELECT SUM(tt.value)
FROM trade tt
WHERE tt.id <= t1.id
) total
FROM trade t1
) t1
where total > 0.8
)
Results:
| COUNT(*) |
|----------|
| 3 |
It seems to me you need aggregate function and cumulative sum need to filter your desired result
set @csum := 0.0;
select count(id) from
(
select id,@csum := @csum + value as sum_val from t
order by id
) t1
where sum_val <1.3
http://sqlfiddle.com/#!9/abf460/2
count(id)
3
