J Livingston SQL (3/10/2014)
maybe of some use
with cte_names as
(
SELECT StudentID, MAX(StudentName) AS StudentName
FROM lcdscores
GROUP BY StudentID
)
SELECT lcdscores.StudentID
, cte_names.StudentName
, CAST ( AVG ( lcdscores.focus1 ) AS decimal ( 9 , 2 )) AS focus1
, CAST ( AVG ( lcdscores.cont1 ) AS decimal ( 9 , 2 )) AS controlling1
FROM
lcdscores INNER JOIN cte_names ON lcdscores.StudentID = cte_names.StudentID
GROUP BY lcdscores.StudentID
, cte_names.StudentName;
In the first sample data, there are a couple of NULL StudentID records. 😀
And the Excel Example is yet another set of data.
I refrained from writing a select statement, thinking that the poster would create one with AVG on a column.
And understand that the Group By creates the distinct records being asked for.
Hopefully they will understand a CTE.