SELECT COUNT... - 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

General SQL Server Forums

New to SQL Server Programming

SELECT COUNT...

Author

Topic

sarka
Starting Member

12 Posts

Posted - 2008-01-09 : 10:36:26

Hallo!
I have a table student(teacherID, studentID, studentName...) that contains data for a certain student and a table teacher(teacherID, teacherName...) that contains data for the teacher. Student can be imagine as follow:

tID | sID ... other fields
----------
1 1
1 2
1 3
1 4
2 5
2 6
2 7
3 8

For example, teacher of tID=1 teachs to 4 students.
I'd like to select all the fields from teacher where the teacher has more than x students.
Is it possible? How can I do?
Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-09 : 11:05:50

use group by . . . having . . .

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 11:10:24

Select t.*
FROM teacher t
INNER JOIN student s
on s.tID=t.tID
GROUP BY t.tID
HAVING COUNT(*) > x

sarka
Starting Member

12 Posts

Posted - 2008-01-09 : 12:26:00

Hi!
Thank you for your answers.
visakh16 "SELECT t.*" gives an error "not a group by expression". Where's the error?
Thank you again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 12:28:37

quote:
Originally posted by sarka

Hi!
Thank you for your answers.
visakh16 "SELECT t.*" gives an error "not a group by expression". Where's the error?
Thank you again!

sorry do like this

select t1.*
FROM teacher t1
INNER JOIN 
(Select t.ID
FROM teacher t
INNER JOIN student s
on s.tID=t.tID
GROUP BY t.tID
HAVING COUNT(*) > x)t2
ON t1.ID=t2.ID

sarka
Starting Member

12 Posts

Posted - 2008-01-10 : 04:08:17

Thanks, it works perfectly!
Just another question: how could I also select the number of student that a professor has and order the data in fuction of this last field? I'd like to obtain that

Pid numberOfStudent
1 10
2 8
3 4
4 1

Thank you!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 04:11:41

quote:
Originally posted by sarka

Thanks, it works perfectly!
Just another question: how could I also select the number of student that a professor has and order the data in fuction of this last field? I'd like to obtain that

Pid numberOfStudent
1 10
2 8
3 4
4 1

Thank you!!!

Modify like this:-

select t1.tID,t2.StudCount
FROM teacher t1
INNER JOIN 
(Select t.ID,COUNT(sID) AS 'StudCount'
FROM teacher t
INNER JOIN student s
on s.tID=t.tID
GROUP BY t.tID)t2
ON t1.ID=t2.ID
ORDER BY t2.StudCount DESC

sarka
Starting Member

12 Posts

Posted - 2008-01-10 : 04:20:09

Wow! You're so fast :)

It looks that AS gives problems:

COUNT(sID) AS 'StudCount'
---------------*
"FROM keyword not found where expected"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 04:33:06

quote:
Originally posted by sarka

Wow! You're so fast :)

It looks that AS gives problems:

COUNT(sID) AS 'StudCount'
---------------*
"FROM keyword not found where expected"

Try this then:-

select t1.tID,t2.StudCount
FROM teacher t1
INNER JOIN 
(Select t.tID,COUNT([sID]) AS 'StudCount'
FROM teacher t
INNER JOIN student s
on s.tID=t.tID
GROUP BY t.tID)t2
ON t1.tID=t2.tID
ORDER BY t2.StudCount DESC

sarka
Starting Member

12 Posts

Posted - 2008-01-10 : 04:46:34

now the error is on the square bracket ([
"missing expression"

do you think it can be a version incompatibility problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 04:51:22

remove it and try

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-10 : 04:51:35

Oh please don't tell me you are using MS-Access and posting in SQL Server forum!!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sarka
Starting Member

12 Posts

Posted - 2008-01-10 : 04:56:48

I'm not an expert but I'm using Oracle SQL. So I suppose it's SQL

I've tried to remove the [ but there's still the error reported above.
I suppose it can't understand the statement AS because it's expecting a FROM clause.
Thank you for your patience!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-10 : 04:59:47

Perhaps you would get better answers posting this question on Oracle forums like www.orafaq.com

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 05:03:43

quote:
Originally posted by sarka

I'm not an expert but I'm using Oracle SQL. So I suppose it's SQL
I've tried to remove the [ but there's still the error reported above.
I suppose it can't understand the statement AS because it's expecting a FROM clause.
Thank you for your patience!

this code worked for me:-

DECLARE @t table
(tid int
)
DECLARE @s table
(
tid int,
[sid] int
)


INSERT INTO @t values (1)
INSERT INTO @t values (2)
INSERT INTO @t values (3)


INSERT INTO @s values (1,1)
INSERT INTO @s values (1,2)
INSERT INTO @s values (1,3)
INSERT INTO @s values (2,1)
INSERT INTO @s values (1,4)
INSERT INTO @s values (2,2)
INSERT INTO @s values (1,5)
INSERT INTO @s values (1,6)
INSERT INTO @s values (2,3)
INSERT INTO @s values (3,1)


select t1.tID,t2.StudCount
FROM @t t1
INNER JOIN 
(Select t.tID,COUNT(sID) AS 'StudCount'
FROM @t t
INNER JOIN @s s
on s.tID=t.tID
GROUP BY t.tID)t2
ON t1.tID=t2.tID
ORDER BY t2.StudCount DESC

output:-

tID         StudCount
----------- -----------
1           6
2           3
3           1
(3 row(s) affected)

sarka
Starting Member

12 Posts

Posted - 2008-01-10 : 05:10:23

well, it still doesn't work. That's very strange...
Thank you for your help, anyway!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 06:19:12

quote:
Originally posted by sarka

well, it still doesn't work. That's very strange...
Thank you for your help, anyway!

As said, if you use ORACLE then post your question at oralce related forums like www.dbforums.com. Same code may not work in both ORACLE and SQL Server

Run this in ORALCE and see what you get

Select data from
(
select 'test' as data
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources