GROUP BY Problem - 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)

GROUP BY Problem

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-01 : 10:48:13

I have four tables which are given below. I have a stored procedure below which has input @UserID and it returns varies
fields. Now, when the student registers for an exam his data is inside the test_userExamRegisteration table. The UserClassCodeCategories does not contain the student userID but the teacher userID. And the student can only access the ExamTypeID using their classCode which is in userClassCodes. Now, I want to display the all the fields which are included in the SPROC below Plus I want to display number of assignments for each classcode. This can be done by counting the number of classcodes for a student inside the test_UserExamRegisteration (Since test_UserExamRegisteration holds the registered assignments for the student). The problem is that when I try to combine test_UserExamRegisteration with some other table I only get the classCode which HAS the assignments. I want all the classCodes for a particular student with number indicating the assignemnts. Something like this:

MATH:1300 (2 Assignments)
CHEM:1310 (0 Assigmemnts)
PHY:4500 (0 Assignments)

@UserID int

AS

SELECT et.Name+':'+ucc.ClassCode AS [CourseName],et.Description
,ucc.ClassCode
FROM UserClassCodeCategories uccc
JOIN test_ExamTypes et ON uccc.ExamTypeID = et.ExamTypeID
JOIN UserClassCodes ucc ON uccc.ClassCode = ucc.ClassCode
WHERE ucc.UserID = @UserID

CREATE TABLE [dbo].[UserClassCodeCategories] (
[UserClassCodeCategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ExamTypeID] [int] NOT NULL ,
[ClassCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UserID] [int] NOT NULL ,
[Active] [bit] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserClassCodes] (
[UserClassCodeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[ClassCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[test_UserExamRegisteration] (
[UserExamRegisterationID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[ExamID] [int] NOT NULL ,
[ClassCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[test_ExamTypes] (
[ExamTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Thanks in advance!

Mohammad Azam
www.azamsharp.net

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 11:13:28

Try

SELECT et.Name+':'+ucc.ClassCode AS [CourseName],et.Description,ucc.ClassCode, Count (*) as Cnt
FROM UserClassCodeCategories uccc
JOIN test_ExamTypes et ON uccc.ExamTypeID = et.ExamTypeID
JOIN UserClassCodes ucc ON uccc.ClassCode = ucc.ClassCode
WHERE ucc.UserID = @UserID
Group By et.Name+':'+ucc.ClassCode, et.Description,ucc.ClassCode

Do u want the brackets etc to be appeaerd with the count or just the figures ?

if the above doesn't work post some sample data as per the point 3 of the following link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Srinika

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-01 : 12:20:19

Hi Thanks, but it is more complicated. Because you need to join the test_UserExamRegisteration table in order to get the students registered exams.

Thanks anyway

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources