Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query Help to Match Multiple Results and Average Expand / Collapse
Author
Message
Posted Monday, March 10, 2014 2:25 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:31 PM
Points: 675, Visits: 6,816
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.
Post #1549483
Posted Monday, March 10, 2014 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:27 PM
Points: 6, Visits: 10
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;





Thank you! Works like I charm. Although I do not know what CTE's are I will study the query you provided to learn more about them.
Post #1549494
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse