Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 This worked for the OP Answer marked as solution


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 763, Visits: 7,797
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 15, 2016 11:20 AM
Points: 47, Visits: 90
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