Select rows from a table where until total sum of a given column is less than 80

There is no way you will reach 18.5 multiple times, because ideally is to keep calculating -5, 6.5, 7, 10 which gives you 18.5 and termintes the select since total sum is already 18.5

Your data shows I can have a negative pnl value.

What if my data is this:

id name pnl date
1 pandglobal -5 1
2 pandglobal 25 2
3 pandglobal 1 3
4 pandglobal -10 4
5 pandglobal 3 5

My subtotals then are:
-5
20
21
11
14

What rows should be returned?

If am to do it in PHP

  1. pull all data from january till date
$total = 0;
$data = $allresult;
$newdata = [];
$i = 0;
while($total < 18.5){
    $pnl = $data[$i]['pnl'];
    $total += $pnl;
    $newdata = $data;
    $i++;
}
return $newdata;

Unless someone else has a better answer, to do it in mysql you’d have to have a subquery for the subtotal (See the post Mab gave), and add a count of the rows greater than your target value; which would make it a JOIN rather than a field in the select; then you’d return rows where that count is 0.

I have two thoughts here

  1. Return all negative and positive pnl so the client can have a better view of how the trading went
  2. Return only positive pnl - just to be direct on how the money was consumed

Note that in this situations each positive value means user is owing us or is being debited of the positive value, but we dont debit negative values from the users balance
See the $20 as margin balance, and if outcome of trade is positive we will deduct that amount from the margin $20 until there is nothing left to deduct.
So once totalpnl is 20 and above we asume the clients margin deposit have been depleted or consumed

no wait that still wouldnt work because the subquery isnt doing its own subquery. Riiight. You’d need 2 layers deep…

I’m gonna say it’ll be easier in PHP. But.

Am not too good with sql joins and none direct queries, and php looks not too good because i may enter on ending loop if care is not taken especially retrieving 200000 rows just to pull out a fraction of data looks weird, thats why i rely on mysql to perform this

Yeah is easier in php but there is a trap am trying to avoid, that is endless or longer loops
if it takes 10,000 plus rows before users deposit is exhausted as to exit the loop then it gives me concern.
Thats why am avoiding php

Well, the MySQL engine has to look at all of the rows as well.

I dont see how that particularly changes whether you do it in MySQL or PHP.

Even if mysql have to look through all that rows it is better as everything is still in sql, no execution time out, no longer loops that my hang my server.
I trust mysql would handle that better than php.
If mysql can query millions of rows but looping millions of rows in php will also not be easy

Lets agree that if we can get it to work in Mysql it will have huge advantage over using php

I… dont agree with that statement. So… no, we wont agree on that :slight_smile:

:smile: we really have to agree oh! like we must make it work with Mysql

Then i will wish you luck, and tell you my answer is you’ll need a joined subquery for flagging when to stop returning which runs a second subquery for doing the subtotals.

And at that point, i will hand you over to the SQL experts who can do better than me, and I will recategorize the post appropriately.

Even if am going to use PHP then i have to compromise integrity and accuracy, because i will employ a method that will exit the loop if total is 18.5 or $i count is 1000
And that means i exit leaving some data behind, i really dont want to compromise

You will ALWAYS exit “leaving some data behind”. PHP or MySQL. Makes no difference.

Thanks Master, but you gave up too easily as opposite to what you have always taught us to be persistent and never give up :smile:

You could have tried a JOIN or any sql that comes to your mind that may work, before handling me over to SQL masters

In mysql will leave just a row, especially when total is 17 and the next pnl is 3 above it will stop, that is only one row
But in PHP using another threshold i may leave hundreds or thousands rows once my threshold is hit

I will need to take a small break and come back, please house anyone with good sql method for this should post
Table name: tradecalls_history
Table coulmns: signalid, tradername , pair, mode, entry, tp , sl , pnl , details ,ontime, status, closedtime

You know that you can fetch the query result row by row and must not fetchAll() to loop over it? So the PHP solution can stop fetching rows as well when it reaches an exit status