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

Databases
1

Please house i have a table called history, with signalid, tradername, pair, pnl,ontime as columns
I want to keep selecting rows where date is greater than strtotime(‘-7 days’) and the total sum of pnl is less than 80
Here are the codes i have tried

$ontime = strtotime('-7 days');
$searchHistory = $olanzarcon->prepare("SELECT signalid,tradername,pair,pnl FROM tradecalls_history HAVING (SELECT SUM(pnl) FROM tradecalls_history WHERE ontime > '$ontime') <= 80");

//Another Attempt
$searchHistory = $olanzarcon->prepare("
    SELECT SUM(pnl) AS total 
    FROM tradecalls_history  
    WHERE ontime >= :ontime 
    GROUP BY signalid, tradername, pair
    HAVING total < 80 ");

The second approach only checks having for the single pnl instead of accumulated sums of pnl that was or is being retrived

Please i need assistant

2

Do you have some data that can be used? The second query looks correct…

3

Yes i have, this is the exported sql

CREATE TABLE `tradecalls_history` (
  `signalid` varchar(100) NOT NULL,
  `tradername` varchar(50) DEFAULT NULL,
  `pair` varchar(50) DEFAULT NULL,
  `mode` text,
  `entry` text,
  `tp` text,
  `sl` text,
  `pnl` varchar(50) DEFAULT '0',
  `details` longtext,
  `ontime` varchar(50) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'processing',
  `closedtime` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tradecalls_history`
--

INSERT INTO `tradecalls_history` (`signalid`, `tradername`, `pair`, `mode`, `entry`, `tp`, `sl`, `pnl`, `details`, `ontime`, `status`, `closedtime`) VALUES
('100000001', 'brunco', 'BTCUSDT', 'BUY', '500', '550', '450', '-5', 'one jakarajaka', '1714290533', 'completed', '1714635091'),
('100000002', 'brunco', 'BTCUSDT', 'BUY', '600', '650', '450', '6.5', 'one jakarajaka', '1714290533', 'completed', '1714635091'),
('100000003', 'brunco', 'ETHUSDT', 'BUY', '600', '650', '450', '7', 'one jakarajaka', '1714290533', 'completed', '1714635091'),
('100000004', 'brunco', 'BTCUSDT', 'BUY', '300', '370', '260', '10', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000005', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '-8.8', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000006', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '3.4', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000007', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '10', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000008', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '9.5', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000009', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '3', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000010', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '10', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000011', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '-4.5', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000012', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '2.6', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000013', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '10', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000014', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '-2.9', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000015', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '10', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000016', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '5.8', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000017', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '2.5', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000018', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '10', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000019', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '3.6', 'one jakarajaka', '1714290533', 'completed', '1714814380'),
('100000020', 'brunco', 'BTCUSDT', 'BUY', '350', '370', '260', '-12', 'one jakarajaka', '1714290533', 'completed', '1714814380');
4

All of your signalid’s are unique. So your groups are only 1 row deep.
GROUP BY signalid, tradername, pair

5

The second query is only counting pnl as single value

6

Yes all signalid is unique, is that why is returning that way?

7

You need to calculate the running total in the query and use it in the HAVING term. Here are some methods to calculate the running total - https://www.geeksforgeeks.org/how-to-compute-a-running-total-in-mysql/

8

Yes. Your groups are 1 row deep. The SUM of a single row is… the single value of that row.

9

Okay now i understand, if the signalid is unique it counts it as one row thereby return single value, but if is a multiple of rows it then count the total?
If thats the case what then should i group it with lets say pair or tradername since they are not unique

10

I saw the subquery version from the link you sent but cant figure how to use it in my case here
I need to modify this

SELECT id, value, (
SELECT SUM(value)
FROM your_table t2
WHERE t2.id <= t1.id
) AS running_total
FROM your_table t1
ORDER BY id;

11

Like, if i have…

id name count store
1 apple 2 a
2 apple 1 a
3 banana 4 a
4 orange 1 a
5 banana 3 a

and i tell my query to GROUP BY store and SUM(count), everything is in one group - they all share the same store, so everything is one group, and I get 11.

If i tell it to GROUP BY store,name, it groups by store, and by name. Store doesnt do anything, but name does, and now I get 3 groups, (a,apple) which contains 2 rows, (a,banana) which contains 2 rows, and (a,orange) which contains 1 row. The SUMs of those will be 3, 7,and1 respectively.

If I tell it to GROUP BY store,id, it groups by store, and by id. ID is unique, so I will end up with 5 groups: (a,1), (a,2), (a,3), (a,4), and (a,5).

GROUP BY will group things using the tuple of fields given to it as a key, and aggregate the groups individually.

12

Thanks alot this is very educative, am getting closer to solving the problem however you pointed me to a very useful thing to note

13

I still have an issue here, if i group by pair only i get the returns of total columns only

But if do like this

$searchHistory = $olanzarcon->prepare("
	SELECT signalid,tradername,pair,pnl,SUM(pnl) AS total 
    FROM tradecalls_history  
    WHERE ontime >= '$ontime' 
    GROUP BY pair
    HAVING total < 80 ");

I get error because of nonaggregate, and if i aggregate by grouping all the field in the select the result becomes awful

14

#1: You’re preparing a query with no parameters in it.
#2: Lets think about this.

Lets say we look at just two rows:

(`signalid`, `tradername`, `pair`, `mode`, `entry`, `tp`, `sl`, `pnl`, `details`, `ontime`, `status`, `closedtime`)
('100000001', 'brunco', 'BTCUSDT', 'BUY', '500', '550', '450', '-5', 'one jakarajaka', '1714290533', 'completed', '1714635091'),
('100000002', 'brunco', 'BTCUSDT', 'BUY', '600', '650', '450', '6.5', 'one jakarajaka', '1714290533', 'completed', '1714635091'),

If you’re grouping by pair, these two rows belong to a group. The SUM of pnl is 1.5. No problem.
Select SignalID. From that group. What value should you get?
A group only returns 1 record.

15

Am getting the point, as for using prepare thats how i always run my queries even if there is something to prepare or not, however for clear understanding i exempted adding where pair = :pair and tradername = :tradername
let me focus on understanding the logic behind this subquery thing ad how to use it effectively

16

It may at this point be more instructive to ask what you WANT the query to generate.

Give us an example of what you want out of the query from the data you gave in post 3.

17

I simply want to return full data details of each rows that is signalid,tradername,pair,pnl,ontime starting from 100000001 to 100000004 where by then the total sum of pnl is now 18.5

So other rows wont be returned immediately our total sum reaches 18.5 and below

('100000001', 'brunco', 'BTCUSDT', 'BUY', '500', '550', '450', '-5', 'one jakarajaka', '1714290533', 'completed', '1714635091'),
('100000002', 'brunco', 'BTCUSDT', 'BUY', '600', '650', '450', '6.5', 'one jakarajaka', '1714290533', 'completed', '1714635091'),
('100000003', 'brunco', 'ETHUSDT', 'BUY', '600', '650', '450', '7', 'one jakarajaka', '1714290533', 'completed', '1714635091'),
('100000004', 'brunco', 'BTCUSDT', 'BUY', '300', '370', '260', '10', 'one jakarajaka', '1714290533', 'completed', '1714814380'),

Because if i dont stop retrieving data when the sum is supposedly over 18.5 it means the search query will return all rows that is greater than the given ontime.

Using php will be a waste bcs i would pull all data to current date and then loop to select or stop loop once the acumulation is 18.5, but is of no use pulling this huge number of rows when all i needed was the exact expenditures till it eats of 18.5

18

I’m gonna be honest, I didnt understand what you said there.

Mab’s right that it sounds like you’re generating a running subtotal, but I still dont understand what you want the actual output to be at the end of the query.

A query does not return multiple times; you get the query result at the end of the query. That’s it.

19

Okay let me simplify.

  1. You are given $18.5 or $20 to trade january last year
  2. Now you have a trade history that has over 200,000 trades in it
  3. The client come this year asking to know how that $20 was consumed or used
  4. If you query from january till date you will have loads of unrelated rows or data to pull out, so the solution is to pull history from january last year and keep adding their pnl till it reaches 18.5, then those rows from january till 18.5 was reached should all be returned to you
20

and what happens if i reach 18.5 multiple times?