CODEWITHSUNDEEP
sqlsqlite
BadmintonCat
BadmintonCat

Reputation: 9586

SQLite - Filter record based on specific calculated sum

I'm given a table with a varying number of records that contain persons, their weight, and an order value which determines in which order the persons should be chosen ...

create table line (
      id int not null PRIMARY KEY,
      name varchar(255) not null,
      weight int not null,
      turn int unique not null,
      check (weight > 0)
  );

I have to retrieve the last record from the table with which 1000 lbs is not exceeded when counting the person's weight together in order.

E.g. if the table is

id | name                | weight  | order |
---+---------------------+---------+-------+
5  | Mark                |   250   |   1   |
4  | James               |   175   |   5   |
3  | John                |   350   |   2   |
6  | James               |   400   |   3   |
1  | Rick                |   500   |   6   |
2  | Mike                |   200   |   4   |

The query should return only James, as the first three picked (per order) people's weight will fit within 1000 lbs. The sum of the weight of these three persons is 250 + 350 + 400 = 1000.

People with same name might be in the table. Therefore the id should be used for the calculation.

How would I write an SQLite statement that can filter this accordingly?

I should first order by order but from there on my SQL knowledge is too limited to figure out how to calculate the sum within 1000...

SELECT name, weight, order FROM line ORDER BY order ASC

Upvotes: 1

Views: 1175

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

We can do this with a correlated subquery:

SELECT
    id,
    name,
    weight,
    "order",
FROM line t1
WHERE
    (SELECT SUM(t2.weight) FROM line t2 WHERE t2."order" <= t1."order") <= 1000
ORDER BY
    "order" DESC
LIMIT 1;

The correlated subquery in the query above calculates the running sum of weight as given by the order. The WHERE clause just restricts to only line records whose cumulative weight does not exceed 1000. Then, we order descending by the order column with LIMIT 1 to target the James record.

By the way, avoid using SQL keywords like order to name your columns.

Upvotes: 6

Mehrdad
Mehrdad

Reputation: 1637

SQL syntax is not case-sensitive. Wrap column name inside []: [order]

SELECT name, weight, order FROM line ORDER BY [order] ASC

Upvotes: 0

Related Questions