2 Replies - 667 Views - Last Post: 19 October 2011 - 06:25 AM Rate Topic: -----

#1 programmingnewbie   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-October 09

Select SQL statement troubles

Posted 19 October 2011 - 04:50 AM

I have a SQL query as follows:
select a.class_id, b.std_id,
( select count(*) from enrollment where class_id = a.class_id ) as class_size,
d.admission_id
from class a, student b, enrollment c, admission d
where
c.class_id = a.class_id and
c.std_id = d.std_id and
c.std_id = b.std_id
order by a.class_id;


The result is:
class_id std_id class_size
comp100 8080 4
comp100 8020 4
comp100 8033 4
comp100 8111 4
comp305 8080 4
comp305 8080 4
comp305 8020 4
comp305 8033 4
comp555 8111 1
comp672 8080 3
comp672 8033 3
comp672 8111 3


But I just want to display one of the results if the class size is more than 1

that is

I would like to do so:
class_id std_id class_size
comp100 8080 4
comp305 8080 4
comp555 8111 1
comp672 8080 3


After thinking a while, I should get the counter first, like this
select enrollment.class_id, count(*) as class_size
from class, enrollment
where enrollment.class_id = class.class_id
group by enrollment.class_id;


then the counter is retrieved as follows:
class_id class_size
comp100 4
comp305 4
comp555 1
comp672 3


however, I don't know how to use one SQL statement to realize the result.

I would like to ask for your advice, how to modify the SQL statement so that I can show only one of the rows if the class size is more than 1.

that is, if the class size is 4, i do not want to show 4 results, instead, i result is enough.

Thank you very much!

This post has been edited by baavgai: 19 October 2011 - 06:10 AM
Reason for edit:: tagged


Is This A Good Question/Topic? 0
  • +

Replies To: Select SQL statement troubles

#2 DarenR   User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Select SQL statement troubles

Posted 19 October 2011 - 05:28 AM

you would use the Top variable

like:

select Top 1 from etc. etc. this wil just show you the top 1
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7197
  • View blog
  • Posts: 15,003
  • Joined: 16-October 07

Re: Select SQL statement troubles

Posted 19 October 2011 - 06:25 AM

You're on the right track. This:
select enrollment.class_id, count(*) as class_size
from class, enrollment
where enrollment.class_id = class.class_id
group by enrollment.class_id;



Can be simplyfied to:
select class_id, count(*) as class_size
	from enrollment
	group by class_id;



You aren't really using class there.

If you just want to see one student... well, you have to pick one.
select class_id, first(std_id) as std_id, count(*) as class_size
	from enrollment
	group by class_id;



We still don't need to pull in any other tables yet. Let's say you wanted that admission_id:
select a.class_id, a.std_id, a.class_size, b.admission_id
	from (
		select class_id, first(std_id) as std_id, count(*) as class_size
			from enrollment
			group by class_id
		) a
		inner join admission b
			on a..std_id = b.std_id



Hope this helps.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1