How to average/aggregate the result off a SQL query?

I have a table with many rows each belonging to different users.

I first want to select all rows the belong to one user and then on each row add the average for that a certain column but can’t make it work.

I’m would like the result to display the average on each row for the user but it’s currently just displaying the value for that particular row.

Example :

CREATE TABLE test 
(
    id INT,
    FOO INT,
    BAR INT,
    X INT,
    user_id INT
);

INSERT INTO test (id, FOO, BAR, X, user_id) VALUES (1, 1, 2, 3, 1);
INSERT INTO test (id, FOO, BAR, X, user_id) VALUES (2, 3, 2, 1, 1);
INSERT INTO test (id, FOO, BAR, X, user_id) VALUES (3, 6, 6, 6, 1);
INSERT INTO test (id, FOO, BAR, X, user_id) VALUES (4, 100, 100, 100, 1);

INSERT INTO test (id, FOO, BAR, X, user_id) VALUES (4, 3000, 3000, 3000, 2);
INSERT INTO test (id, FOO, BAR, X, user_id) VALUES (4, 400, 400, 400, 2);

And the query I’m trying to run:

SELECT 
    id, foo, bar, x,
    AVG(foo) AS "avg_foo",
    AVG(bar) AS "avg_bar",
    AVG(x) AS "avg_x"
FROM 
    test
WHERE
    user_id = 1
GROUP BY
    id, foo, bar, x;

But I’m just getting the same number that is in the “foo” column for the row as the result for avg_foo where instead I’m trying to get the average of all the “foo” rows displayed on each rows (the would be the same number every time).

Currently I’m getting this result :

| id  | foo | bar | x   | avg_foo                | avg_bar              | avg_x                  |
| --- | --- | --- | --- | ---------------------- | -------------------- | ---------------------- |
| 1   | 1   | 2   | 3   | 1.00000000000000000000 | 2.0000000000000000   | 3.0000000000000000     |
| 2   | 3   | 2   | 1   | 3.0000000000000000     | 2.0000000000000000   | 1.00000000000000000000 |
| 3   | 6   | 6   | 6   | 6.0000000000000000     | 6.0000000000000000   | 6.0000000000000000     |
| 4   | 100 | 100 | 100 | 100.0000000000000000   | 100.0000000000000000 | 100.0000000000000000   |

Whereas I’m expecting:

| id  | foo | bar | x   | avg_foo                | avg_bar              | avg_x                  |
| --- | --- | --- | --- | ---------------------- | -------------------- | ---------------------- |
| 1   | 1   | 2   | 3   | 27.5      | 27.5    | 27.5      |
| 2   | 3   | 2   | 1   | 27.5      | 27.5    | 27.5      |
| 3   | 6   | 6   | 6   | 27.5      | 27.5    | 27.5      |
| 4   | 100 | 100 | 100 | 27.5      | 27.5    | 27.5      |

---

I’ve made a fiddle

https://www.db-fiddle.com/f/5tGQkqEnqB8et75eJ7NNWt/0

Answer

Use window functions:

SELECT id, foo, bar, x,
       avg(foo) over (partition by user_id) as "avg_foo",
       avg(bar) over (partition by user_id) as "avg_bar",
       avg(x) over (partition by user_id) as "avg_x"
FROM test
WHERE user_id = 1

Advertisement

Answer