CODEWITHSUNDEEP
sqlsql-server
Det
Det

Reputation: 39

MSSQL How do I get average of four records from different tables?

I have four tables and I need to find the average of each score for a particular id. I do not need the ENTIRE Column average, but the average of each record with the same id in each table. I have tried this:

SELECT DISTINCT  M.system_id, S.name, SUM(A.Score + WAL.score + F.score + WIN.score) / 4 
AS avgScore 
FROM         dbo.T3_MovementSystemJoin AS M 
INNER JOIN dbo.T3_systems AS S ON M.system_id = S.id
INNER JOIN T3_ApplicationSystemJoin AS A ON A.Application_id = @application_id
INNER JOIN T3_WallTypeSystemJoin AS WAL ON WAL.wall_id = @wall_id
INNER JOIN T3_FenestrationSystemJoin AS F ON F.fenestration_id = @fen_id
INNER JOIN T3_WindowOrientation_System AS WIN ON WIN.window_id = @window_id
INNER JOIN T3_ConstructionSystemJoin AS C ON C.contruction_id = @construction_id
INNER JOIN T3_JointDepthSystemJoin AS J ON J.JointDepth_id = @JointDepth_id
INNER JOIN T3_JointGapSystemJoin AS JG ON JG.JointGap_id = @JointGap_id
WHERE     (M.movement_id = @movement_id)
GROUP BY M.System_id, S.name

:

Thanks for your help!

Upvotes: 0

Views: 52

Answers (3)

Anon
Anon

Reputation: 10908

If you don't want NULL values to become zeros and included in the average:

SELECT DISTINCT  M.system_id, S.name, X.avgScore
FROM         dbo.T3_MovementSystemJoin AS M 
INNER JOIN dbo.T3_systems AS S ON M.system_id = S.id
INNER JOIN T3_ApplicationSystemJoin AS A ON A.Application_id = @application_id
INNER JOIN T3_WallTypeSystemJoin AS WAL ON WAL.wall_id = @wall_id
INNER JOIN T3_FenestrationSystemJoin AS F ON F.fenestration_id = @fen_id
INNER JOIN T3_WindowOrientation_System AS WIN ON WIN.window_id = @window_id
INNER JOIN T3_ConstructionSystemJoin AS C ON C.contruction_id = @construction_id
INNER JOIN T3_JointDepthSystemJoin AS J ON J.JointDepth_id = @JointDepth_id
INNER JOIN T3_JointGapSystemJoin AS JG ON JG.JointGap_id = @JointGap_id
CROSS APPLY ( SELECT AVG(s) FROM (VALUES (A.Score),(WAL.score),(F.score),(WIN.score) ) scores(s) ) X(avgScore)
WHERE     (M.movement_id = @movement_id)
GROUP BY M.System_id, S.name

Upvotes: 0

Milen
Milen

Reputation: 8877

No Sum needed (and no grouping too)

SELECT DISTINCT  M.system_id, S.name,  (IsNull(A.Score, 0) +   IsNull(WAL.score, 0) +  IsNull(F.score, 0) +  IsNull(WIN.score, 0)) /4
as avgscore
FROM         dbo.T3_MovementSystemJoin AS M 
INNER JOIN dbo.T3_systems AS S ON M.system_id = S.id
INNER JOIN T3_ApplicationSystemJoin AS A ON A.Application_id = @application_id
INNER JOIN T3_WallTypeSystemJoin AS WAL ON WAL.wall_id = @wall_id
INNER JOIN T3_FenestrationSystemJoin AS F ON F.fenestration_id = @fen_id
INNER JOIN T3_WindowOrientation_System AS WIN ON WIN.window_id = @window_id
INNER JOIN T3_ConstructionSystemJoin AS C ON C.contruction_id = @construction_id
INNER JOIN T3_JointDepthSystemJoin AS J ON J.JointDepth_id = @JointDepth_id
INNER JOIN T3_JointGapSystemJoin AS JG ON JG.JointGap_id = @JointGap_id
WHERE     (M.movement_id = @movement_id)

Upvotes: 1

TTeeple
TTeeple

Reputation: 2989

SELECT DISTINCT M.system_id
,S.name
,(ISNULL(A.Score,0) + ISNULL(WAL.score,0) + ISNULL(F.score,0) + ISNULL(WIN.score,0)) /4 as 'AvgScore'
FROM         dbo.T3_MovementSystemJoin AS M 
INNER JOIN dbo.T3_systems AS S ON M.system_id = S.id
INNER JOIN T3_ApplicationSystemJoin AS A ON A.Application_id = @application_id
INNER JOIN T3_WallTypeSystemJoin AS WAL ON WAL.wall_id = @wall_id
INNER JOIN T3_FenestrationSystemJoin AS F ON F.fenestration_id = @fen_id
INNER JOIN T3_WindowOrientation_System AS WIN ON WIN.window_id = @window_id
INNER JOIN T3_ConstructionSystemJoin AS C ON C.contruction_id = @construction_id
INNER JOIN T3_JointDepthSystemJoin AS J ON J.JointDepth_id = @JointDepth_id
INNER JOIN T3_JointGapSystemJoin AS JG ON JG.JointGap_id = @JointGap_id
WHERE     (M.movement_id = @movement_id)

Upvotes: 0

Related Questions