mysql join two tables with aggregations-CodePudding

I am trying to join two tables based on an id and do aggregations in the final result.

Table1

id name val
0  A    10
1  B    20
2  C    10

Table2

id cat price
0  1   10
0  2   20
1  1   10
1  2   10
2  1   10


SELECT t1.*, t2. FROM 
Table1 t1
JOIN Table2 t2 ON t1.id = t2.id;

I'd like to join the two tables and in the final result aggregate the matching rows in Table2. Aggregation function is Average. Final result:

id name val price
0  A    10  15
1  B    20  10
2  C    10  10

CodePudding user response：

When using aggregate functions, you must use a GROUP BY clause to inform the DBMS which attributes to aggregate by. It's also good practice to name the result in a way that indicates the aggregation you used.

SELECT t1.id, t1.name, t1.val, avg(t2.price) as avg_price
FROM Table1 t1
JOIN Table2 t2 on t2.id = t1.id
GROUP BY t1.id, t1.name, t1.val

CodePudding user response：

select t1.id, t1.name, t1.val, avg(t2.price) as price 
from Table1 as t1 
join Table2 as t2
   on t1.id = t2.id
group by t1.id, t1.name, t1.val