PostgreSQL Excluding values from average

Question

I am calculating the average of values in a table

CREATE TABLE measurements (
  id SERIAL PRIMARY KEY,
  measurement INTEGER NOT NULL
);

import postgres from "https://deno.land/x/postgresjs@v3.3.3/mod.js";

const averageMeasurement = async() => {
    const rows =  await sql`SELECT AVG(measurement) AS average FROM measurements`;
    return rows[0].average;
}
const sql = postgres({});

export{averageMeasurement}

How can I exclude values that are larger than 1000 or smaller than 0 from the calculation of average?

I got Internal Server Error when I tried

import postgres from "https://deno.land/x/postgresjs@v3.3.3/mod.js";

const averageMeasurement = async() => {
    const excMeasurements = await sql`SELECT * FROM measurements WHERE measurement <= 1000 AND measurement > 0`
    const rows =  await sql`SELECT AVG(measurement) AS average FROM excMeasurements`;
    return rows[0].average;
}
const sql = postgres({});

export{averageMeasurement}

Answer 1

You can simply add a WHERE predicate:

SELECT AVG(measurement) AS average FROM measurements
  WHERE measurement >= 0 AND measurement <= 1000