How to get avarge rating of the paired data in sqlite3

user_16065150 0
Viewed 24

I a some paired dataset like below (table):

ID1 ID2 Rating
1 2 2
1 4 4
2 4 3
2 5 1

I want to use sqlite3 query to extract the average paired rating for each ID. The output should be:

Item 1: average(2,4);
Item 2: average(2,3,1);
Item 4: average(4,3);
Item 5: average(1);

However, the only way I can think about is:

SELECT id1, AVG(rating)
FROM table
GROUP BY id1;

This will only give me the average rating of:

Item 1: average(2,4);
Item 2: average(3,1)

May I ask how can I get my desired output by modifying the query?

sqlite python
1 Answers

You can use union all with a cte:

with cte(id, rating) as (
   select id1, rating from ratings
   union all
   select id2, rating from ratings
)
select id, ave(rating) from cte group by id;
user_73267380
Related