CODEWITHSUNDEEP
sqlsqlite
user44136
user44136

Reputation: 25

SQLite: average of column indexed by two columns

Given the following table

+----+----+------+
|id1 |id2 |value |
+----+----+------+
| 1  | 2  | 10   |
| 1  | 3  | 20   |
| 1  | 4  | 30   |
| 2  | 3  | 10   |
| 2  | 4  | 40   |
| 3  | 4  | 10   |
+----+----+------+

I want to have avg(value) of each id, whether located the id1 or id2 column.

Thus, the output should be:

1,20

2,20

3,16.66

4,26.6

Help would be greatly appreciated.

Upvotes: 1

Views: 37

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

You could use UNION ALL:

WITH cte AS (
  SELECT id, value FROM tab
  UNION ALL
  SELECT id2, value FROM tab
)
SELECT id, AVG(value) AS value
FROM cte
GROUP BY id;

DBFidde Demo

Upvotes: 3

Related Questions