Martins Martins - 1 month ago 10
SQL Question

How to join together data by ID and calculate average value, sql

I want to join together two tables and receive result as below. Plus i want to receive AVG value of activity based on User ID.
I was trying this:

SELECT Users.ID, (SELECT AVG(Activity) FROM Activity) AS AVG_Activity, Users.Date_started
FROM Users
INNER JOIN Activity
ON Users.ID=Activity.ID
ORDER BY `ID`;


But this give me result where i have ID rows as in Activity table (User with ID 1, 2,3 are shown more than once), AVG_activity row shows all user activity average value not grouped on ID's and Date started is OK as based on User ID's.

Users table:

ID Date_started
1 2016-08-03
2 2016-09-12
3 2016-09-13


Activity table:

ID Activity Activity_Date
1 0.5 2016-09-01
2 0.5 2016-10-01
3 0.7 2016-10-01
1 0.1 2016-10-01
1 0.2 2016-11-01
2 0.5 2016-11-01
3 0.8 2016-11-01


Result that i want:

ID AVG_Activity Date_started
1 0.26 2016-08-03
2 0.5 2016-09-12
3 0.75 2016-09-13

Answer

You just need to use GROUP BY:

SELECT U.ID, 
       AVG(A.Activity) AVG_Activity,
       U.Date_started
FROM Users U
INNER JOIN Activity A
ON U.ID = A.ID
GROUP BY U.ID, U.Date_started;