question

wwwacny avatar image
wwwacny asked

looking to get the average by joining two tables

I have one table containing a list of objects, then another table with tasks for those objects. I'm looking to return the average of the tasks in one query. This is the objects table select o.ID, o.Name, o.Description from tbl_objects o ID Name 90 Car This is the tasks table select t.DataID, t.ID, t.PercentComplete from tbl_tasks t where t.id = 90 DataID ID PercentComplete 44276 90 10 44277 90 40 44283 90 60 44284 90 70 I would like to create one SQL Query that will give me this basically select o.ID, o.Name, Average as (select AVG(t.PercentComplete) from tbl_tasks t where t.ID = o.ID) so the result will be ID Name Average 90 Car 45
average
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
wwwacny avatar image
wwwacny answered
Found it. I forgot I had a similar query to get the count, I changed it for avarage select t2.Name, t1.ID,t1.average from ( select ID,AVG(PercentComplete) as "average" from tbl_invData s group by ID ) t1 inner join ( select ID, Name from tbl_invs ) t2 on t2.ID = t1.ID order by t2.Name
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.