Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Question Unanswered: Using count(*) in where question

    Hello everyone,

    I am new in here, had already search the rest of the forum and find out here is probably the biggest. I read all my books but still having problem for the display my sub query commands.

    I try to display the name of the major that has the largest number of students. and here are things what I came out so far.

    at the first time I try to do this, but of course it doesn't work

    select maj_code, max(count(maj_code))
    from student
    group by maj_code;

    ERROR at line 1:
    ORA-00937: not a single-group group function

    Therefore I go check my book and find out i should suppose using sub query.
    and this is what i think...

    Select maj_code, count(maj_code)
    From student
    Where count(maj_code) =(select max(count(maj_code))
    from student
    group by maj_code);

    and here is the result
    ERROR at line 3:
    ORA-00934: group function is not allowed here

    And I have been thinking for the whole day but couldn't come out with a better way. wondering if anybody can help?

    thanks in advance~

  2. #2
    Join Date
    Oct 2009
    Posts
    4
    oh i just found out how to do it..

    select maj_code, count(*)
    from student
    group by maj_code
    having count(*) =(select max(count(*))
    from student
    group by maj_code)

    but sorry I didn't mention the whole question
    that is display the name of the major that has the least number of advisors but the largest number of students.

    and now i am having

    MAJ_CODE COUNT(*)
    -------------------- ----------
    ACCT 3

    for student

    min advisor

    MAJ_CODE COUNT(*)
    -------------------- ----------
    CSCI 1
    FINC 1
    HIST 1
    INST 1

    i think i just make everything wrose......
    still ..anyone could help??

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by antigenius
    select max(count(*)) ...
    that's invalid syntax

    so, yeah... wrose
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by antigenius
    but sorry I didn't mention the whole question
    that is display the name of the major that has the least number of advisors but the largest number of students.
    What if there are ties (such as seventy-two majors that only have one student)? What if the majors with the fewest advisors are not the majors with the most students?

    Going to your teacher or TA would be your best bet, they understand what they're trying to teach (or we hope they understand it anyway) and can deal with specific problems much better than we can. This is apparently an "Introduction to Oracle" class, but more details on the class would help. Can you post a link to the assignment so we can read it as the teacher originally posted it?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2009
    Posts
    4
    Quote Originally Posted by r937
    that's invalid syntax

    so, yeah... wrose
    Haha..actually it works. but the thing is it doesn't come up with the answer I want so yeah....worse....

  6. #6
    Join Date
    Oct 2009
    Posts
    4
    Quote Originally Posted by Pat Phelan
    What if there are ties (such as seventy-two majors that only have one student)? What if the majors with the fewest advisors are not the majors with the most students?

    -PatP
    Thank you PatP. and yes that's the problem that I am facing now. the majors i got form the database is not the most student. sign~ I sent a email to my TA and my teacher already last night already but haven't get any reply yet. thanks for the advise.

    here is the detail.(class note)

    create table advisor
    (adv_code varchar2(08) constraint advisor_adv_code_pk primary key,
    adv_lname varchar2(15),
    adv_fname varchar2(15),
    adv_phone varchar2(12),
    sch_code varchar2(8) constraint advisor_sch_code_fk references school (sch_code));

    insert into advisor values (‘A1’, ‘Porter’, ‘Mattie’, ‘281-283-3163’, ‘BUS’);
    insert into advisor values ( ‘A2’, ‘Grady’, ‘Perdue’ ,‘281-283-3400’, ‘BUS’);
    insert into advisor values (‘A3’, ’Tran’, ’Van’, ‘281-283-3203’, ‘BUS’);
    insert into advisor values (‘A4’, ‘Saleem’, ‘Naveed’, ‘281-283-3202’, ‘BUS’);
    insert into advisor values (‘A5’, ‘Kwok-Bon’, ‘Yue’, ‘281-283-3864’, ‘SCE’);
    insert into advisor values (‘A6’, ‘Jones’, ‘Lisa’, ‘281-283-3551’, ‘EDU’);
    insert into advisor values (‘A7’, ‘Palmer’, ‘Bruce’, ‘281-283-3445’, ‘HSH’);

    create table major
    (maj_code varchar2(10) constraint major_maj_code_pk primary key,
    maj_desc varchar2(30),
    sch_code varchar2(8) constraint major_sch_code_fk references school(sch_code));

    insert into major values (‘ACCT’, ‘Accounting’, ‘BUS’);
    insert into major values (‘FINC’, ‘Finance’, ‘BUS’);
    insert into major values (‘ISAM’, ‘Management Information Systems’, ‘BUS’);
    insert into major values (‘CSCI’, ‘Computer Science’, ‘SCE’);
    insert into major values (‘HIST’, ‘History’, ‘HSH’);
    insert into major values (‘INST’, ‘Instructional Technology’, ‘EDU’);

    create table maj_adv
    (maj_code varchar2(10) constraint maj_adv_maj_code_fk references major (maj_code),
    adv_code varchar2(08) constraint maj_adv_adv_code_fk references advisor (adv_code),
    constraint maj_adv_maj_code_adv_code_cpk primary key (maj_code, adv_code));

    insert into maj_adv values (‘ACCT’, ’A1’);
    insert into maj_adv values (‘ACCT’, ‘A2’);
    insert into maj_adv values (‘FINC’, ’A2’);
    insert into maj_adv values (‘ISAM’, ’A3’);
    insert into maj_adv values (‘ISAM’, ’A4’);
    insert into maj_adv values (‘CSCI’, ‘A5’);
    insert into maj_adv values (‘INST’, ‘A6’);
    insert into maj_adv values (‘HIST’, ‘A7’);

    create table student
    (std_code varchar2(9),
    std_lname varchar2(15) constraint student_std_lname_nn not null,
    std_fname varchar2(15) constraint student_std_fname_nn not null,
    std_gend varchar2(8),
    maj_code varchar2(10) constraint student_maj_code1_fk references major (maj_code),
    std_dob date,
    constraint student_std_code_pk primary key (std_code));

    insert into student values (‘S1’, ‘Jordan’, ‘Michael’, ‘F’, ‘FINC’, to_date(‘10-Mar-1962’, 'DD-Mon-YYYY'));
    insert into student values (‘S2’, ‘Barkley’, ‘Charles’, ‘M’, ‘ACCT’, to_date(‘12-Sep-1964’, 'DD-Mon-YYYY'));
    insert into student values (‘S3’, ‘Johnson’, ’Magic’, ‘M’, ‘ACCT’, to_date(‘13-Sep-1960’, 'DD-Mon-YYYY'));
    insert into student values (‘S4’, ‘Williams’, ‘Serena’, ‘F’,‘ISAM’, to_date(‘23-Oct-1980’, 'DD-Mon-YYYY'));
    insert into student values (‘S5’, ‘Duncan’, ‘Tim’, ‘M’, ‘ISAM’, to_date(‘07-Aug-1972’, 'DD-Mon-YYYY'));
    insert into student values (‘S6’, ‘Graff’, ’Steffi’, ‘F’, ‘CSCI’, to_date(‘30-Apr-1962’, 'DD-Mon-YYYY'));
    insert into student values (‘S7’, ‘Navratilova’, ’Martina’, ‘F’, ’ACCT’, to_date(‘18-Dec-1972’, 'DD-Mon-YYYY'));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •