Trouble with GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trouble with GROUP BY

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-09-29 : 10:57:23

Hi,

I am having trouble to write a query. Here is the situation. I have a UserExams table which contains the score of the users. I want to capture the higest score of a particular user and put it into a group. The groups are created at runtime or dynamically and I want to know how many people belong in particular groups.

Here is my query:

SELECT
'Group' = CASE
WHEN MAX(ue.Score) BETWEEN 0 AND 59 THEN '0-59'
WHEN MAX(ue.Score) BETWEEN 59 AND 69 THEN '59-69'
WHEN MAX(ue.Score) BETWEEN 69 AND 79 THEN '69-79'
WHEN MAX(ue.Score) BETWEEN 79 AND 89 THEN '79-89'
WHEN MAX(ue.Score) BETWEEN 90 AND 100 THEN '90-100'
END

FROM
UserExams ue
JOIN Users u ON ue.UserID = u.UserID
JOIN Exams e ON ue.ExamID = e.ExamID

GROUP BY 'Group'

And here is what I need to get:

GROUP NoOfStudentsInThisGroup
0-59 5
59-68 2
79-89 8
90-100 3

Thanks,
Azam

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-09-29 : 11:27:40

I think I got it. I created a temp table and inserted the group names in the temp table and then later use the group by to group according to the group names.

Here is the running query:

INSERT INTO #UserExamGroups(GroupName)
SELECT 'Group' = CASE
WHEN MAX(ue.Score) BETWEEN 0 AND 59 THEN '0-59'
WHEN MAX(ue.Score) BETWEEN 59 AND 69 THEN '59-69'
WHEN MAX(ue.Score) BETWEEN 69 AND 79 THEN '69-79'
WHEN MAX(ue.Score) BETWEEN 79 AND 89 THEN '79-89'
WHEN MAX(ue.Score) BETWEEN 89 AND 100 THEN '89-100'
END

FROM
UserExams ue
JOIN Users u ON ue.UserID = u.UserID
JOIN Exams e ON ue.ExamID = e.ExamID

GROUP BY
u.FirstName,u.LastName,e.Title

SELECT GroupName,COUNT(GroupName) FROM #UserExamGroups
GROUP BY GroupName

Mohammad Azam
www.azamsharp.net

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-29 : 11:32:50

No need to use temp tables...here is one possible way:

select 
	(case
		when ue.Score between 0 and 59 then '0-59'
		when ue.Score BETWEEN 59 AND 69 THEN '59-69'
		WHEN ue.Score BETWEEN 69 AND 79 THEN '69-79'
		WHEN ue.Score BETWEEN 79 AND 89 THEN '79-89'
		WHEN ue.Score BETWEEN 90 AND 100 THEN '90-100' 
	end) as [group],
	count(ue.score) as NoOfStudentsInThisGroup
FROM
UserExams ue 
JOIN Users u ON ue.UserID = u.UserID 
JOIN Exams e ON ue.ExamID = e.ExamID 
Group by 
	(case
		when ue.Score between 0 and 59 then '0-59'
		when ue.Score BETWEEN 59 AND 69 THEN '59-69'
		WHEN ue.Score BETWEEN 69 AND 79 THEN '69-79'
		WHEN ue.Score BETWEEN 79 AND 89 THEN '79-89'
		WHEN ue.Score BETWEEN 90 AND 100 THEN '90-100' 
	end)

Harsh Athalye
India.
"Nothing is Impossible"

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-09-29 : 18:05:56

Thanks for the great query. But I need to select the max score of an individual in the exam.

Meaning if I give the exam1 ten times then I only want to select my highest score.

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-30 : 02:26:20

Select Id,max(score) as Score from table
Group by Id

Or post some sample data and with expected result

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-30 : 08:55:58

Somthing like this


SELECT
'Group' = CASE MAX(ue.Score)
WHEN  BETWEEN 0 AND 59 THEN '0-59'
WHEN  BETWEEN 59 AND 69 THEN '59-69'
WHEN  BETWEEN 69 AND 79 THEN '69-79'
WHEN  BETWEEN 79 AND 89 THEN '79-89'
WHEN  BETWEEN 90 AND 100 THEN '90-100'
END,Count(ue.Score)

FROM
UserExams ue
JOIN Users u ON ue.UserID = u.UserID
JOIN Exams e ON ue.ExamID = e.ExamID

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-01 : 02:45:13

Aha i guess this should work


Select [Group], Count([Group]) As [Count] From
(
  SELECT
  'Group' = 
   CASE WHEN MAX(ue.Score) BETWEEN 0 AND 59 THEN '0-59'
   CASE WHEN MAX(ue.Score) BETWEEN 59 AND 69 THEN '59-69'
   CASE WHEN MAX(ue.Score) BETWEEN 69 AND 79 THEN '69-79'
   CASE WHEN MAX(ue.Score) BETWEEN 79 AND 89 THEN '79-89'
   CASE WHEN MAX(ue.Score) BETWEEN 90 AND 100 THEN '90-100'
   END
   FROM
   UserExams ue
   JOIN Users u ON ue.UserID = u.UserID
   JOIN Exams e ON ue.ExamID = e.ExamID
   Group by [ID]
) as f

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources