-1

I have an sql table trade with following data

id| value | price
 1| 0.1   |1
 2| 0.5   |2
 3| 0.9   |2
 4| 0.3   |2

How do I make an SQL query so that I get the count of entries limited to total value of 0.9 for price 2 ascending by id . For example:-

Select Count of id FROM trade WHERE sum(value) <= 0.9 and price = '2'

Result should come as

 3

As there are total 3 entries with id 2,3,4 with values 0.5,0.9,0.3 with price 2 . The sum of them is 1.7 which is more than 0.9 but if we take 0.5 and 0.3 it combines to 0.8 which is lesser than 0.9 . So result should be 3 as it consists value of atleast 0.9 with price 2.
Also is there way to get the id of the results with last and first highest value in order.

So it looks like :-

4
2
3

Help appreciated :)

yunzen
  • 32,854
  • 11
  • 73
  • 106
JeffB
  • 191
  • 1
  • 15
  • Unclear... Do you want to count items with `price=2` and `value <= 0.9`? Would this query: `SELECT COUNT(*) FROM trade WHERE value <= 0.9 AND price = '2'` do what you want? If not, please give an example where it would return a wrong result and explain why. – Alex Shesterov Sep 08 '18 at 16:00
  • Yes somewhat like that. But in `SELECT COUNT(*) FROM trade WHERE value <= 0.9 AND price = '2'` it should be the sum of value <=9 or something like that – JeffB Sep 08 '18 at 16:02
  • In *your* example, you stated that the result should be `3`. Should it be `2` instead? as the sum of 2 values is <= 0.9, and the sum of 3 values is > 0.9. Can you please explain this point? – Alex Shesterov Sep 08 '18 at 16:09
  • The sum of 3 values is >=9, yes thats what I want. I want it that the main value(0.9) should come up with sum of x value out of y entries where x > 0.9 but when we split x , x-1 entry should be < 0.9 – JeffB Sep 08 '18 at 16:12
  • 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.9 ) `this works but i want to add the price parameter as well i.e `WHERE price= '2'` . – JeffB Sep 08 '18 at 16:12
  • Ok, now I understand what you need :) – Alex Shesterov Sep 08 '18 at 16:48
  • Now as you did, please if possible provide me the query :) – JeffB Sep 08 '18 at 16:50
  • It is: https://en.wikipedia.org/wiki/Knapsack_problem – Lukasz Szozda Sep 11 '18 at 14:46
  • What is wrong with `SELECT COUNT(id) FROM trade WHERE price = 2 HAVING MIN(value) <= 0.9`? This ensures that at least one item in the "2" group is less than 0.9. – Salman A Sep 13 '18 at 08:53

3 Answers3

1

You can achieve this by using a temporary SQL variable which stores partial sums and used rows count. Note that there are two SQL statements: 

SET @tempsum := 0, @rowscount := 0;

SELECT MIN(tempcount) FROM 
  (SELECT 
      (@rowscount := @rowscount + 1) AS tempcount, 
      (@tempsum := @tempsum + value) AS tempsum 
   FROM trade WHERE 
   price='2' 
   ORDER BY value
  ) AS partialsums 
WHERE tempsum>=0.9;

This way the partial sums are built only once. Without variables, you would need to build another subquery which builds multiple partial sums.

Here the SQL Fiddle: http://sqlfiddle.com/#!9/38fc2/11/1

See also: Create a Cumulative Sum Column in MySQL

You may also use variables to store the IDs involved, i.e.: 

SET @tempsum := 0, @rowscount := 0, @ids := '';

SELECT MIN(tempcount), tempids FROM 
  (SELECT 
      (@tempsum := @tempsum + value) AS tempsum,
      (@rowscount := @rowscount + 1) AS tempcount,
      (@ids := concat(@ids, ' ', id)) AS tempids
   FROM trade WHERE 
   price='2' 
   ORDER BY value
  ) AS partialsums 
WHERE tempsum>=0.9;

See Fiddle: http://sqlfiddle.com/#!9/38fc2/33/0

Alex Shesterov
  • 26,085
  • 12
  • 82
  • 103
1
select id from 
    (select id, if(not(@sum > 0.9), 1, 0) mark,  (@sum:=@sum+value) as sum 
        from trade cross join  (select @sum:=0) s  
        where price=2 order by value asc) t 
where mark =1

The inner query counts cumulative sum and addional field mark, which is equal one while sum is less and turn into zero when it is over 0.9. Since it's working one step later, it gathers the first row where sum is above the limit.

The result of the inner select

id   mark   sum
4    1      0.30000001192092896
2    1      0.800000011920929
3    1      1.699999988079071

Now in the outer query you just need to select rows with mark equal 1. And it results in 4,2,3

demo on sqlfiddle

splash58
  • 26,043
  • 3
  • 22
  • 34
  • Perfect , lets say there is one more field type having either buy or sell, is there a way to choose from WHERE type='buy' ? – JeffB Sep 14 '18 at 10:57
  • Also a way to get ids except last? – JeffB Sep 14 '18 at 10:59
  • 1) You can expand where clause `where price=2 ` as wide as you want 2) swap fields order in select list, or just not use last id in ther result list – splash58 Sep 14 '18 at 12:12
  • So lets say result is 45,59,595,3434 how do i get only 3434 or remove it – JeffB Sep 14 '18 at 17:47
  • You get an array of id as a result of the query. Just unset the last item of this array, for example – splash58 Sep 14 '18 at 18:56
0

If you need the count of the distinct ID values you could use count(distinct id)

and do the fact you checking for an aggregated result (sum() ..) you should use having and not where 

Select Count(distinct id ) 

FROM trade 
where price = 2
HAVING sum(value) <= 0.9

if you want the count for the rows with ID not null theb you could use count(id)

Select Count(id ) 
FROM trade 
 where price = 2
HAVING sum(value) <= 0.9

NB you are using price as a string

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107