SQL - using sum() to retrieve the first row where it gets to a value

Question

I have this table, let's call it Lines:

id | id_sale | id_product | quantity
1      1           1           4
2      2           1           4
3      3           1           4
4      4           1           4

It represents the lines of a sale.

And I want to fetch the id of the line where the product gets sold X times. For something like this I believe that an order by id asc is needed.

For instance, with the previous data, for product 1 and to X = 6, I would get the id 2. Or for X = 10 I would get the id 3.

I have found a question that's pretty much what I need: use mysql SUM() in a WHERE clause

I have tried the correct answer (adapted for SQL) but in SQL I can't add the order by id inside the subquery, and because of this I believe the query stops making sense.

Here is what I've tried so far:

select y.id, y.quantity 
from (select t.id, 
            t.quantity, 
            (select sum(x.quantity) 
                from Lines x
                where x.id <= t.id) as atmTotal
        from Lines t where t.id_product = 1) y
where y.atmTotal >= 10
order by y.ID;

Result: This is just returning all id's (and quantities) that have the product 1.

you are using sql-server as tagged correct? You probably can use window functions to make this a little easier. Your narative of your data doesn't match your example data (everything is quantity 4 in your table). Can you edit to improve your sample data and provide a desired output example. — Matt, Jun 28 '16 at 17:13
Sorry, but I need to do this in a query. But my data matches the narative.. (It doesn't matter if the quantity is always 4). I'll edited the desired output. Thanks. — Duarte Mendes, Jun 28 '16 at 17:16
So you want the ID of when the running total goes over X amount. The "Running Total" is key here and not well understood which is why I and Eric asked for clarification. Juan Carlos's answer is what I would have posted for that too unless you are using an rdbms that doesn't have window functions. — Matt, Jun 28 '16 at 17:45

score 2 · Accepted Answer · answered Jun 28 '16 at 17:18

2

SQL DEMO

SELECT TOP 1 *
FROM (
        SELECT *,
               SUM([quantity]) OVER (ORDER BY [id]) as total // or ORDER [sales_id]
        FROM Lines
     ) T
WHERE total <= 6  // your X
ORDER BY total desc

answered Jun 28 '16 at 17:18

Juan Carlos Oropeza

47,252
12
78
118

I changed it to: select TOP 1 T.id_sale from ( select *, sum([quantity]) over (order by [id]) as total from Lines where id_product = 4 ) T where total >= 10 order by total asc; And it's just what I need, many thanks! – Duarte Mendes Jun 28 '16 at 17:52
Ohh I see, glad you can fix it. I understand the problem upside down :P – Juan Carlos Oropeza Jun 28 '16 at 18:43

score 0 · Answer 2 · answered Jun 28 '16 at 17:24

0

  select x.id ,sum(y.quantity) 
                    from Lines x,
                         Lines y
                where y.id <= x.id
    group by x.id
    having sum(y.quantity)  >= 10

answered Jun 28 '16 at 17:24

Kostya

1,567
1
9
15

Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. – Juan Carlos Oropeza Jun 28 '16 at 17:32

SQL - using sum() to retrieve the first row where it gets to a value

2 Answers2