Group By - eller?

BjurisMedlem sedan juli 2001514 inlägg

Frågan1 juli 2008 07:44#1

en sp:

ALTER PROCEDURE tuser887.GetSavedBaseByStudentID @StudentID uniqueidentifier
AS

	Select answerId, reportId, studentId, Base.BaseName, Topic.TopicName,  answers.GoalId, Answer, Comment, [TimeStamp], CreatedBy,
	sum(case when Answer = 0 then 1 else 0 end) as iGreen,
	sum(case when Answer = 1 then 1 else 0 end) as iOrange,
	sum(case when Answer = 2 then 1 else 0 end) as iRed,
	as totalQuestions
	from MyPlan_Answers AS Answers
	Left Outer JOIN MyPlan_Base AS Base
	ON Base.BaseId = Answers.BaseId
	Left Outer JOIN MyPlan_Goal AS Goal
	ON Goal.GoalId = Answers.GoalId
	Left Outer JOIN MyPlan_Topic AS Topic
	ON Topic.TopicId = Goal.TopicId
	Where studentId = @StudentID
	Group BY CreatedBy, answerId, reportId, studentId, Base.BaseName, Topic.TopicName,  answers.GoalId, Answer, Comment, [TimeStamp], Base.BaseId
	Order by createdBy, Base.Basename, Topic.TopicName
	

RETURN

Denna returnerar en rad för varje post
ex:
Admin - 3 - Slöjd - 1 iGreen - 0 iOrange - 0 iRed
Admin - 3 - Slöjd - 0 iGreen - 1 iOrange - 0 iRed
Admin - 3 - MA- 1 iGreen - 0 iOrange - 0 iRed
Admin - 3 - MA- 1 iGreen - 0 iOrange - 0 iRed
osv

Men jag vill att det skall hämtas ut en rad per | Admin - 3 - slöjd | samt summera antal iGreen - summera antal iOrange - summera antal iRed

ex:
Admin - 3 - Slöjd - 1 iGreen - 1 iOrange - 0 iRed
Admin - 3 - MA- 2 iGreen - 0 iOrange - 0 iRed

Hur skall jag göra?

BjurisMedlem sedan juli 2001514 inlägg

1 juli 2008 08:10#2

never mind.

löste felet.

ALTER PROCEDURE tuser887.GetSavedBaseByStudentID @StudentID uniqueidentifier
AS

	Select Base.BaseName, Topic.TopicName, CreatedBy,
	sum(case when Answer = 0 then 1 else 0 end) as iGreen,
	sum(case when Answer = 1 then 1 else 0 end) as iOrange,
	sum(case when Answer = 2 then 1 else 0 end) as iRed,
	(SELECT  COUNT(*) FROM MyPlan_Goal i WHERE  i.BaseId = Base.BaseId and i.TopicId = Topic.TopicID) as totalQuestions
	from MyPlan_Answers AS Answers
	Left Outer JOIN MyPlan_Base AS Base
	ON Base.BaseId = Answers.BaseId
	Left Outer JOIN MyPlan_Goal AS Goal
	ON Goal.GoalId = Answers.GoalId
	Left Outer JOIN MyPlan_Topic AS Topic
	ON Topic.TopicId = Goal.TopicId
	Where studentId = @StudentID
	Group BY CreatedBy, Base.BaseName, Topic.TopicName, Base.BaseId, Topic.TopicId
	Order by createdBy, Base.Basename, Topic.TopicName
	

RETURN