Get count of rows limit value sql php

Question

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

please edit you question and provide information what do you expect as result — hotfix, Sep 03 '18 at 08:50

D-Shih · Accepted Answer · 2018-09-03T09:22:40.623

2

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 |

edited Sep 03 '18 at 09:22

answered Sep 03 '18 at 08:53

D-Shih

44,943
6
31
51

Its returning 2 ...i want 3. For example the first three ids sum up to 1.5 . So 0.8 < 1.5 ...it should return first 3 id – JeffB Sep 03 '18 at 08:56
@JeffB Ok I see Wait a second – D-Shih Sep 03 '18 at 08:58
@JeffB What's your mysql version? – D-Shih Sep 03 '18 at 09:09
Server version: 5.7.23 - MySQL Community Server (GPL) – JeffB Sep 03 '18 at 09:12
#1046 - No database selected – JeffB Sep 03 '18 at 09:29
Is there a way I can get the ids of those as well? – JeffB Sep 03 '18 at 09:34
Thanks appreciate u alot :D – JeffB Sep 03 '18 at 09:36
How do I only get the last Id? – JeffB Sep 03 '18 at 11:36
And everything except last id? – JeffB Sep 03 '18 at 11:48
Can you please have a look at https://stackoverflow.com/questions/52236678/select-count-of-rows-in-mysqli – JeffB Sep 08 '18 at 16:25

Zaynul Abadin Tuhin · Answer 2 · 2018-09-03T09:21:12.933

1

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

edited Sep 03 '18 at 09:21

answered Sep 03 '18 at 08:50

Zaynul Abadin Tuhin

31,407
5
33
63

1

i do not understand. the id is always unique, so group by id is unnecessary – hotfix Sep 03 '18 at 08:53
@hotfix it seems to me op has multiple value for single user but yes your point is valid but from his query try i guess that – Zaynul Abadin Tuhin Sep 03 '18 at 08:54
I wanted count not select id – JeffB Sep 03 '18 at 08:55
@JeffB then use simple count and where clause – Zaynul Abadin Tuhin Sep 03 '18 at 08:56
Its returning 2 on my side – JeffB Sep 03 '18 at 09:00
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179306/discussion-between-jeffb-and-zaynul-abadin-tuhin). – JeffB Sep 03 '18 at 09:02
@JeffB if it help please accept answer as SO appreciation – Zaynul Abadin Tuhin Sep 03 '18 at 09:02
Wait when i try select count(id) from trade where amount <1.3, its returning 4 which is wrong. It should return 3. i want it to check ascending order id sum – JeffB Sep 03 '18 at 09:04
@JeffB could you give sample of your real data cause i have not found 1.3 in sample data – Zaynul Abadin Tuhin Sep 03 '18 at 09:06
@JeffB all the values are below 1.3 so it returns 4 how it will return 3 ?? – Zaynul Abadin Tuhin Sep 03 '18 at 09:09
U didnt understand the question...it should add all the values by id ascending so first 3 add up as 1.5. So i want a query in which it returns count of entries by ascending order where limit sum 1.3. Meaning result should come as 3 as 1.3<1.5 of first three entries – JeffB Sep 03 '18 at 09:11
returning me as 2 – JeffB Sep 03 '18 at 09:33
In my database it is returning 2 idk why – JeffB Sep 03 '18 at 09:34
I know but idk why not for my sql .... Anyways the answer above urs works just fine – JeffB Sep 03 '18 at 09:37

Get count of rows limit value sql php

2 Answers2

Linked