Question 185538 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – PostgreSQL Excluding values from average

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/[email protected]/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/[email protected]/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}
Tags:

2

Answers


  1. 0

    You can simply add a WHERE predicate:

    SELECT AVG(measurement) AS average FROM measurements
  WHERE measurement >= 0 AND measurement <= 1000
    Login or Signup to reply.
  2. 0

    The previous answer is correct, but modern versions of Postgres also support:

    SELECT AVG(measurement) AS overall_average
, AVG(measurement) FILTER(WHERE measurement >= 0 AND measurement <= 1000 ) as between_average
FROM measurements
WHERE TRUE;

    This is nice because it lets you easily return different averages in the same query and should at worst require one scan of the table vs having multiple queries. Of course, if you’re just getting that single average-within-a-range value then filtering the data in the WHERE clause means that it’s easy for Postgres to filter the data earlier. See aggregate expressions documentation for more information.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search