CODEWITHSUNDEEP
sqlite
zumzum
zumzum

Reputation: 20198

get average of a range of rows using OFFSET and LIMIT?

I have a table with about 1000 rows. I want to query for the average value on column "Things" from row 0 to row 79.

I do the following:

SELECT AVG("Things") FROM "MyTable" LIMIT 80 OFFSET 0

At first it seemed like it worked, I get a number back.

then I tried to run this query just to test things:

SELECT AVG("Things") FROM "MyTable" LIMIT 80 OFFSET 10

and I get nothing back. Whatever number I put in the OFFSET I get nothing back unless I use zero (... OFFSET 0)

I am not sure why that is. Any idea?

Upvotes: 1

Views: 585

Answers (2)

CL.
CL.

Reputation: 180162

The OFFSET/LIMIT clauses applay to the result rows of a query. With "LIMIT 80", you are saying that you want no more than 80 rows back.

AVG() returns only one row, so the LIMIT clause has no effect whatsoever.

To filter rows before AVG() is applied, you need to use WHERE, or a subquery. Please note that there is no guaranteed order of rows unless you're using ORDER BY, so you always should use ORDER BY when using LIMIT:

SELECT AVG(Things)
FROM (SELECT Things
      FROM MyTable
      ORDER BY ...
      LIMIT 80 OFFSET ...);

Upvotes: 1

EricS
EricS

Reputation: 9768

I verified the problem in sqlite 3.8.5 on my Mac. I don't understand why it doesn't work, but I'm not exactly a SQL expert.

As a workaround, you can do something like:

select avg("Things") from MyTable where (rowid>=10) and (rowid<=90);

This isn't exactly the same thing if you've deleted rows, though.

Here's a better way:

select avg("Things") from (select Things from MyTable limit 80 offset 10);

Upvotes: 1

Related Questions