SQL Join is not working when adding a third value to SELECT

Question

Slowly going insane wondering why my SQL query works with 2 SELECT variables, but not 3. This is being used in Solarwinds.

SELECT TOP 20 ROUND(AVG(t1.Availability), 2) as Average_Availability, t2.Caption as Node_Name
FROM Orion.ResponseTime t1
LEFT JOIN Orion.Nodes t2 ON t1.NodeID=t2.NodeID
GROUP BY Node_Name ORDER BY Average_Availability

But when I add another value to the SELECT statement from the same table I joined (Orion.Nodes), e.g.:

    SELECT TOP 20 ROUND(AVG(t1.Availability), 2) as Average_Availability, t2.Caption as Node_Name, 
t2.Status as CurrentStatus
    FROM Orion.ResponseTime t1
    LEFT JOIN Orion.Nodes t2 ON t1.NodeID=t2.NodeID
    GROUP BY Node_Name ORDER BY Average_Availability

It stops working completely. I feel like the answer is staring me right in the face but I can't figure it out. I think I need another JOIN function, but not sure how to match the values to t1.NodeID

Answer 1

This query is aggregating rows to calculate the AVG(), but you are only grouping by one of your other fields, Node_Name. You need to add CurrentStatus to the GROUP BY list

Answer 2

You are missing a column of current status that you need to add to group by as that is the part of your select statement. Try running it after that and that error should go away!