SQLITE Query AVG SubSelect / Join

user_9506931 0
Viewed 30

I tried my best, but I'm not able to deal with this :) I have two tables, I try to compare the AVG Data from Table one with the Data2 from Table2.

Table1

Name Data
AA 1
AA 5
AA 10
AB 10
AB 50
AB 100

AVG AA = 5.333... AVG AB = 53.333...

Table2

Name2 Data2
AA 4
AB 50

The output I want:

Name Data Name2 Data2
AA 5.333... AA 4
AB 53.333 AB 50

I tried something like this...

SELECT (SELECT AVG(Data) FROM Table1) AS AVGData, (SELECT Name FROM Table1) AS Name, Table2.*, FROM "Table2"
GROUP BY Table2.Name
join sqlite average
1 Answers

Assuming that both tables contain the same names, you must aggregate in Table1 and then join to Table2:

SELECT t2.*, t1.* 
FROM (
  SELECT Name, AVG(Data) Data 
  FROM Table1
  GROUP BY Name
) t1 INNER JOIN Table2 t2
ON t2.Name2 = t1.Name
user_104988280
Related