Count user in detail table based on centre

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)

Count user in detail table based on centre

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2006-08-28 : 00:38:50

Hi,

i am working on student evaluation. I have two table 1 is centre which includes different centres.

Second table contains results of evaluation given by student on the basis of centre. Student can given no of evaluation on different teachers.

I want to calculate how many student has given evaluation on different centre. Result should be like that

Centre No. of Student
A 250
B 400
C 300

Structure of Table
Centre table:
CCODE
CNAME

Evaluation:
eval_m_sno
ccode [centre]
code [student code]

pls tell me the query to generate the above result.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-28 : 00:55:01

Is this a homework assignment???

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-28 : 01:08:19

[code]
Select count(1) As [Number of Students],E.CName From Evaluation E Inner Join Centre c on E.CCode = C.CCode
Group by
E.CName
[/code]

Chirag

imughal
Posting Yak Master

192 Posts

Posted - 2006-08-28 : 03:01:06

Dear Chirag,

CName is in centre table not in evaluation table

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-28 : 03:04:20

opps.


Select count(1) As [Number of Students],C.CName From Evaluation E Inner Join Centre c on E.CCode = C.CCode 
Group by 
C.CName

Chirag

imughal
Posting Yak Master

192 Posts

Posted - 2006-08-29 : 02:37:23

Thanks but is not giving actual result. if student has given more than one time evaluation it should be counted at ones. no repeating entry. so pls tell me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 02:39:52

Please post your table structure, some sample data and the result that you want. It is much clearer this way. Else we might trying to guess what you want.

imughal
Posting Yak Master

192 Posts

Posted - 2006-08-29 : 03:22:30

adm_centre [table name]
CCODE
CNAME

Sample Data
1 Centre A
2 Centre B
3 Centre C

eval_res_master [Table Name]
eval_m_sno
eval_date
ccode
code [Student Code]

Sample Data
1 22/08/2006 1 STD1
2 22/08/2006 1 STD1
3 22/08/2006 1 STD1
4 22/08/2006 1 STD2
5 22/08/2006 1 STD2
6 22/08/2006 1 STD3
7 22/08/2006 2 STD4
8 22/08/2006 2 STD5
9 22/08/2006 3 STD6
10 22/08/2006 3 STD6
11 22/08/2006 3 STD7
12 22/08/2006 3 STD8
13 22/08/2006 3 STD9

Result Set
Centre Total Student
1 3
2 2
3 4

Hope it is complete info.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-29 : 03:35:00

[code]
Select count(1) As [Number of Students],E.CName
From adm_centre E Inner Join eval_res_maste c on E.CCode = C.CCode
Where C.eval_m_sno = (Select Max(eval_m_sno) From eval_res_maste t
Where t.CCode = c.CCode)
Group by
E.CName
[/code]

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 03:35:30

"Hope it is complete info."
Yes.

declare @adm_centre table
(
	ccode	int,
	cname	varchar(100)
)

declare @eval_res_master table
(
	eval_m_sno	int,
	eval_date	datetime,
	ccode		int,
	code		varchar(100)
)

insert into @adm_centre
select	1, 'Centre A'	union all
select	2, 'Centre B'	union all
select	3, 'Centre C'

set dateformat dmy

insert into @eval_res_master
select	 1, '22/08/2006', 1, 'STD1'	union all
select	 2, '22/08/2006', 1, 'STD1'	union all
select	 3, '22/08/2006', 1, 'STD1'	union all
select	 4, '22/08/2006', 1, 'STD2'	union all
select	 5, '22/08/2006', 1, 'STD2'	union all
select	 6, '22/08/2006', 1, 'STD3'	union all
select	 7, '22/08/2006', 2, 'STD4'	union all
select	 8, '22/08/2006', 2, 'STD5'	union all
select	 9, '22/08/2006', 3, 'STD6'	union all
select	10, '22/08/2006', 3, 'STD6'	union all
select	11, '22/08/2006', 3, 'STD7'	union all
select	12, '22/08/2006', 3, 'STD8'	union all
select	13, '22/08/2006', 3, 'STD9'

select	c.ccode, count(distinct code) as Total_Student
from	@adm_centre c inner join @eval_res_master e
	on	c.ccode	= e.ccode
group by c.ccode

/*
ccode       Total_Student 
----------- ------------- 
1           3
2           2
3           4

(3 row(s) affected)
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources