Group by problems - 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 problems

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-08-07 : 14:33:36

Hi,

I have a table called test_Exams and test_ExamBatches. the table schema is given below:

Now, I want to select ExamID, Title, Description and other stuff from the test_Exams table on the basis of the class Code. Since, each batch can contain multiple exams and I only want to return a single copy of the exam in a particular batch so I use this query.

DECLARE @ClassCode varchar(10)
SET @ClassCode = '2873'

-- get the information about the exams
SELECT e.ExamID,e.Title,e.Description,e.TotalQuestions,
et.Name AS [CategoryName],eb.Title AS [BatchTitle],e.IsEditable,
ed.StartDate, ed.EndDate
FROM test_Exams e
JOIN test_ExamTypes et ON e.ExamTypeID = et.ExamTypeID
JOIN test_ExamBatches eb ON e.ExamBatchID = eb.ExamBatchID
JOIN test_ExamDates ed ON e.ExamID = ed.ExamID

WHERE e.ExamID IN
(
SELECT
MIN(ExamID) as ExamID
--ExamTypeID
FROM
test_Exams WHERE ClassCode = @ClassCode
GROUP BY
ExamBatchID
)

The query works fine. But now I want to find that how many exams are in a particular batch i.e No of exams in a batch.

How can I do that?

CREATE TABLE [dbo].[test_ExamBatches] (
[ExamBatchID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[test_Exams] (
[ExamID] [int] IDENTITY (1, 1) NOT NULL ,
[ExamBatchID] [int] NOT NULL ,
[ExamTypeID] [int] NOT NULL ,
[Title] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ShortTitle] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Duration] [int] NOT NULL ,
[TotalQuestions] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL ,
[ExamStatusID] [int] NOT NULL ,
[IsEditable] [bit] NOT NULL ,
[NoOfAttempts] [int] NULL ,
[IsAttemptEditable] [bit] NOT NULL ,
[IsDateEditable] [bit] NOT NULL ,
[IsDurationEditable] [bit] NOT NULL ,
[GradeScoreTypeID] [int] NOT NULL ,
[IsGradeVisibleDateEditable] [bit] NOT NULL ,
[IsPenaltyEnabled] [bit] NOT NULL ,
[GradeVisibleOptionID] [int] NOT NULL ,
[ClassCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Mohammad Azam
www.azamsharp.net

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-07 : 18:01:55

[code]

select
Exam_Count = count(*)
from
(
select
aa.ExamID
from
test_Exams aa
group by
aa.ExamID
) a

[/code]

CODO ERGO SUM

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-07 : 21:06:07

Don't you mean:

SELECT ExamBatchID, COUNT(*) as cnt
FROM test_Exams
GROUP BY ExamBatchID

Rob Farley
http://robfarley.blogspot.com

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-08-08 : 12:21:52

Hi Rob_Farley,

Yes, that will work but the thing is that I need to select the feilds from the Exam table as well and if I put those feilds in the select statement then I have to include them in the GROUP BY and if I do that then it won't return the COUNT properly.

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-08 : 13:01:35

or

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-08-08 : 13:11:37

Thanks,

Here is a sample scenario.

test_Exams table

ExamID ExamBatchID Title Description TotalQuestions
1 1 Exam1 hahah 45
2 1 Exam1 hahah 45
3 1 Exam1 hahah 45
4 2 Exam3 jj 4
5 2 Exam3 kk 4

And here is the test_ExamBatches table

ExamBatchID Title
1 Exam Batch 1
2 Exam Batch 3

Now, I want the following to be returned

ExamID ExamBatchID Title Description TotalQuestions NoOfExams
1 1 Exam1 hahah 45 3
4 2 Exam3 kk 4 2

I just need to return a single exam from every batches. But I also need to return the total no of exams in that batch.

Mohammad Azam
www.azamsharp.net

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-08 : 20:34:35

Ah, then you want the OVER clause.

SELECT *, COUNT(*) OVER (PARTITION BY ExamBatchID) as cnt
FROM test_Exams

Rob Farley
http://robfarley.blogspot.com

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-09 : 00:08:31

... or if you're not using SQL2005, try:

SELECT te1.*, te2.cnt
FROM
test_Exams te1
JOIN
(SELECT te.ExamBatchID, COUNT(*) as cnt FROM test_Exams te GROUP BY te.ExamBatchID) te2
on te2.ExamBatchID = te1.ExamBatchID

Rob Farley
http://robfarley.blogspot.com

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-08-09 : 11:32:25

Thanks Rob,

There is a little problem that I am facing. The query:

SELECT te1.*, te2.cnt
FROM
test_Exams te1
JOIN
(SELECT te.ExamBatchID, COUNT(*) as cnt FROM test_Exams te
GROUP BY te.ExamBatchID
) te2
on te2.ExamBatchID = te1.ExamBatchID
WHERE te1.ClassCode = '2873'

returns all the rows which match the criteria and I only want to select a single exam from a batch. The query is returning the results something like this:
ExamID ExamBatchID ExamTypeID Title
26 8 1 AP CALCULUS EXAM 2
27 8 1 AP CALCULUS EXAM 2
28 8 1 AP CALCULUS EXAM 2
29 8 1 AP CALCULUS EXAM 2
30 8 1 AP CALCULUS EXAM 2
20 16 1 EXAM 1 AP CAL 2873
18 16 1 EXAM 1 AP CAL 2873
19 16 1 EXAM 1 AP CAL 2873
14 16 1 EXAM 1 AP CAL 2873
15 16 1 EXAM 1 AP CAL 2873
16 16 1 EXAM 1 AP CAL 2873
17 16 1 EXAM 1 AP CAL 2873
6 16 1 EXAM 1 AP CAL 2873
7 16 1 EXAM 1 AP CAL 2873
8 16 1 EXAM 1 AP CAL 2873
9 16 1 EXAM 1 AP CAL 2873
10 16 1 EXAM 1 AP CAL 2873
11 16 1 EXAM 1 AP CAL 2873
12 16 1 EXAM 1 AP CAL 2873
13 16 1 EXAM 1 AP CAL 2873

and I want the results to return like this:

26 8 1 AP CALCULUS EXAM 2
20 16 1 EXAM 1 AP CAL 2873

see only two copies of the exam which are filtered based on the ExamBatchID. It does not matter which copies since all are same.

Mohammad Azam
www.azamsharp.net

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-09 : 20:27:42

Hmm. Looks like you have normalisation issues, but anyway...

In SQL2005, you can do:

with examsCTE as (
SELECT *, COUNT(*) OVER (PARTITION BY ExamBatchID) as cnt, ROW_NUMBER() OVER (ORDER BY ExamBatchID, ExamID) as rn
FROM test_Exams
)
select *
from examsCTE
where rn = 1

Or in SQL2000, how about:

SELECT te1.*, te2.cnt
FROM
test_Exams te1
JOIN
(SELECT te.ExamBatchID, COUNT(*) as cnt, MIN(te.ExamID) as ExamID
FROM test_Exams te
GROUP BY te.ExamBatchID
) te2
on te2.ExamID = te1.ExamID
WHERE te1.ClassCode = '2873'

Whether or not you use the WHERE clause, this should give you only one result per ExamBatchID, because you're now joining to the Exams by the smallest ExamID for each Batch.

Rob Farley
http://robfarley.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources