CODEWITHSUNDEEP
androidsqlsqlite
Simeon Shopkin
Simeon Shopkin

Reputation: 63

SQLite SUM() between several rows

I need some help with the SUM feature in android app. I have a table that looks something like the following :

enter image description here

I have need to SUM Quantities between last two records Notes and last one record with Note. I need to sum Quantity of rows 31,32 and 33. It would return 90. I've tried 

SELECT Sum(QUANTITY) FROM fuel_table WHERE NOTE!='' ORDER BY ID DESC

but it returns SUM of all quantities with note.

Upvotes: 5

Views: 1770

Answers (6)

Iamat8
Iamat8

Reputation: 3906

Use sub query to get your QUANTITY in deceasing order by ID and LIMIT 3 as you want last 3 row and put SUM() to the result quantity...

SELECT SUM(QUANTITY) FROM (SELECT QUANTITY FROM fuel_table ORDER BY ID DESC LIMIT 3);

Upvotes: 0

Andomar
Andomar

Reputation: 238176

WITH    max_id_with_note AS
        (
        SELECT  MAX(ID) AS max_id
        FROM    YourTable
        WHERE   IFNULL(note, '') <> ''
        )
,       previous_max_id_with_note AS
        (
        SELECT  max(ID) as max_id
        FROM    YourTable
        WHERE   IFNULL(note, '') <> ''
                AND ID < (SELECT max_id FROM max_id_with_note)
        )
SELECT  SUM(Quantity)
FROM    YourTable
WHERE   (SELECT max_id FROM previous_max_id_with_note)
        < ID and ID <= 
        (SELECT max_id FROM max_id_with_note)

Example at SQL Fiddle.

Upvotes: 2

Viktor
Viktor

Reputation: 51

I created table like you have and test. Andomar had good idea but made few mistakes!

WITH    max_id_with_note AS
        (
        SELECT  MAX(ID) AS max_id
        FROM    fuel_table
        WHERE   Note <> ''
        )
,       previous_max_id_with_note AS
        (
        SELECT  max(ID) as max_id
        FROM    fuel_table
        WHERE   Note <> ''
                AND ID < (SELECT max_id FROM max_id_with_note)
        )
SELECT  SUM(Quantity)
FROM    fuel_table
WHERE          (SELECT max_id FROM previous_max_id_with_note)
        < id and id <= (SELECT max_id FROM max_id_with_note)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I am inclined to phrase the question as: sum the quantity from all rows that have one note "ahead" of them. This suggests:

select sum(quantity)
from (select ft.*,
             (select count(*)
              from fuel_table ft2
              where ft2.note = 'Yes' and ft2.id >= ft.id
             ) as numNotesAhead
      from fuel_table ft
     ) ft
where numNotesAhead = 1;

Upvotes: 4

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

Change your query as

SELECT Sum(QUANTITY) FROM fuel_table ORDER BY ID DESC LIMIT 3

Upvotes: 0

Viktor
Viktor

Reputation: 51

First select few ROW and from this selection query SUM(). In your case it looks like this:

Select SUM(t1.QUANTITY) FROM (SELECT QUANTITY from fuel_table WHERE NOTE!='' ORDER BY ID limit 2) as t1

Upvotes: 0

Related Questions