How to join the result set of Common Table Expression with other existing table in sql server 2005?

I want to join the result set of common table expression with the existing table. The problem arise using the group by clause as given in the following query. Can anyone please tell me how to join those two tables?.

With CTEQuery
as
(SELECT     StudentOnlineExamCourseAnswer.StudentI开发者_运维问答D, StudentOnlineExamCourseAnswer.OnlineExamID, StudentOnlineExamCourseAnswer.CourseID, 
                      StudentOnlineExamCourseAnswer.CentreID,
case QuestionBank.ComplexLevelID when 1 then (2) when 2 then (4) when 3 then (6) when 4 then (8) when 5 then (10) end as Mark
FROM         QuestionBank INNER JOIN
                      StudentOnlineExamCourseAnswer ON QuestionBank.Answer = StudentOnlineExamCourseAnswer.Answer AND 
                      QuestionBank.QuestionID = StudentOnlineExamCourseAnswer.QuestionID) 
select StudentID, OnlineExamID ,CourseID , CentreID , sum(Mark) as 'Total Marks' from CTEQuery 
group by StudentID, OnlineExamID ,CourseID , CentreID 

---

You can define multiple CTEs for a single select, and each CTE can reference previously defined ones. So you can do:

With CTEQuery
as
(SELECT     StudentOnlineExamCourseAnswer.StudentID, StudentOnlineExamCourseAnswer.OnlineExamID, StudentOnlineExamCourseAnswer.CourseID, 
                      StudentOnlineExamCourseAnswer.CentreID,
case QuestionBank.ComplexLevelID when 1 then (2) when 2 then (4) when 3 then (6) when 4 then (8) when 5 then (10) end as Mark
FROM         QuestionBank INNER JOIN
                      StudentOnlineExamCourseAnswer ON QuestionBank.Answer = StudentOnlineExamCourseAnswer.Answer AND 
                      QuestionBank.QuestionID = StudentOnlineExamCourseAnswer.QuestionID)
, SummarizedCTE as ( 
select StudentID, OnlineExamID ,CourseID , CentreID , sum(Mark) as TotalMark from CTEQuery 
group by StudentID, OnlineExamID ,CourseID , CentreID)
select <new query involving joining SummarizedCTE with the "other table" referenced in your discussion>

---

You need to create your CTE first, then use it in the next SELECT statement.

WITH cteTest (Column1) AS 
(
    SELECT column1
      FROM table1
)

SELECT *
  FROM cteTest
  JOIN Table2
    ON cteTest.column1 = Table2.column1

---

example

with demoCTE 
as
(
  select id from table1
) select * from demoCTE
    join table2
   on demoCTE.id= table2.id