Home » SQL & PL/SQL » SQL & PL/SQL » Simple query involving count
Simple query involving count [message #285149] Mon, 03 December 2007 13:41 Go to next message
CMcKeen
Messages: 3
Registered: December 2007
Junior Member
Ok, I've been working on this for hours and been getting increasingly frustrated.

I have three applicable tables in my hypothetical educational database: Faculty, Attends, and Section:

CREATE TABLE Faculty (
fss_num		VARCHAR2(20)	CONSTRAINT faculty_nn_fss_num NOT NULL,
name		VARCHAR2(20)	CONSTRAINT faculty_nn_name NOT NULL,
address		VARCHAR2(20),
age		NUMBER,
salary		NUMBER(10,2),
CONSTRAINT pk_faculty PRIMARY KEY (fss_num)
);

CREATE TABLE Section (
c_num		NUMBER		CONSTRAINT section_nn_c_num NOT NULL,
sec_num		NUMBER		CONSTRAINT section_nn_sec_num NOT NULL,
fss_num		VARCHAR2(20),
CONSTRAINT pk_section PRIMARY KEY (c_num, sec_num),
CONSTRAINT fk1_section FOREIGN KEY (c_num) REFERENCES Course(c_num),
CONSTRAINT fk2_section FOREIGN KEY (fss_num) REFERENCES Faculty(fss_num)
);

CREATE TABLE Attends (
sss_num		VARCHAR2(20)	CONSTRAINT attends_nn_sss_num NOT NULL,
c_num		NUMBER		CONSTRAINT attends_nn_c_num NOT NULL,
sec_num		NUMBER		CONSTRAINT attends_nn_sec_num NOT NULL,
CONSTRAINT pk_attends PRIMARY KEY (sss_num, c_num),
CONSTRAINT fk1_attends FOREIGN KEY (sss_num) REFERENCES Student(sss_num),
CONSTRAINT fk2_attends FOREIGN KEY (c_num, sec_num) REFERENCES Section(c_num, sec_num)
);



I'm trying to create a query that lists the name of each faculty member and the number of students they teach. This is what I have:

SELECT faculty.name "Name", count(attends.sss_num) "Total# of Students" 
FROM Faculty, Attends, Section
 WHERE section.fss_num (+) = faculty.fss_num
       AND attends.c_num = section.c_num
       AND attends.sec_num = section.sec_num
  GROUP BY name; 


This gets me everything except the faculty that don't have related sections, so I get the right numbers for those that do, but there isn't a single 0 in my results.

Any ideas?
Thanks!

[Updated on: Mon, 03 December 2007 13:52] by Moderator

Report message to a moderator

Re: Simple query involving count [message #285150 is a reply to message #285149] Mon, 03 December 2007 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow the posting guidelines as stated in URL above;
especially those involving the use of <code tags> & proving test data
Re: Simple query involving count [message #285160 is a reply to message #285149] Mon, 03 December 2007 15:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will give you a clue. It is to do with your outer join. Search for it and you will understand why you are not getting your expected results.

Good luck

Regards

Raj
Re: Simple query involving count [message #285484 is a reply to message #285160] Tue, 04 December 2007 20:39 Go to previous messageGo to next message
CMcKeen
Messages: 3
Registered: December 2007
Junior Member
Well, I looked up information on outer joins, and I'm still kind of confused.

At first I thought the problem was because I was doing the outer join on the wrong two tables since I'm trying to retrieve information from the Faculty and an aggregate from the Attends table but I'm joining the Faculty and Section tables, but I can't think of a way to directly link the Faculty and Attends tables since they're not directly related. And I got yelled at when I tried to make outer joins between more than one set of tables. Joining the Attends and Section table didn't work either, though I just did that one as a last resort.

Here's the example result that I forgot to put in the first post:
NAME                 COUNT(ATTENDS.SSS_NUM)
-------------------- ----------------------
finster                                   1
greggory                                  1
will                                      1
tymann                                    2
strouse                                   1
jones                                     2
rich                                      1
loder                                     1

NAME                 COUNT(ATTENDS.SSS_NUM)
-------------------- ----------------------
judd                                      1
kilber                                    4
hardy                                     2
williams                                  2
moriarty                                  1
holmes                                    1
brown                                     3
brindle                                   1

NAME                 COUNT(ATTENDS.SSS_NUM)
-------------------- ----------------------
bowden                                    1

17 rows selected.


Oh, and I'm using SQL*Plus: Release 10.2.0.3.0
Re: Simple query involving count [message #285485 is a reply to message #285484] Tue, 04 December 2007 20:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Outer joins must be chained.

If you outer join B to A, and then inner join C to B, then you lose the benefit of the outer join.

Ross Leishman
Re: Simple query involving count [message #285486 is a reply to message #285149] Tue, 04 December 2007 21:05 Go to previous message
CMcKeen
Messages: 3
Registered: December 2007
Junior Member
Aha! I misunderstood the error when I tried to use an outer join on multiple tables, but I got it working now. Thanks!
Previous Topic: I am facing Error,
Next Topic: To calculate the no. of entries of transactions in database
Goto Forum:
  


Current Time: Sat Dec 10 04:52:59 CST 2016

Total time taken to generate the page: 0.08649 seconds