Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    13

    Unanswered: SQL group by question

    Let say I want to select information as state in following SQL:

    <SQL>
    SELECT code_orgstructure.ext_desc,
    (select users.givenname from users where users.userid =
    transcript_module.userid),
    (select users.familyname from users where users.userid =
    transcript_module.userid),
    (select learningObject.learningid from learningObject where
    learningObject.learningid = transcript_module.learningid),
    (select learningObject.title from learningObject where
    learningObject.learningid = transcript_module.learningid),
    avg(CONVERT(float, AICC_core.highestscore))
    FROM transcript transcript_module, code_orgstructure code_orgstructure,
    AICC_core AICC_core, users users
    WHERE users.userid = transcript_module.userid AND AICC_core.session_id
    = transcript_module.contentSessionID AND users.leaf_orgid =
    code_orgstructure.orgid GROUP BY code_orgstructure.ext_desc
    </SQL>

    Is it possible to get the same information without subquery? At first I
    thought it can be done with

    <SQL>
    SELECT code_orgstructure.ext_desc, max(CONVERT(float,
    AICC_core.highestscore)), users.familyname, users.givenname, learningObject.learningid, learningObject.title FROM transcript transcript_module, code_orgstructure code_orgstructure, learningObject
    learningObject, AICC_core AICC_core, users users
    WHERE users.userid = transcript_module.userid AND learningObject.learningid = transcript_module.learningid AND transcript_module.userid = users.userid
    AND transcript_module.learningid = learningObject.learningid AND
    transcript_module.contentSessionID = AICC_core.session_id AND
    users.leaf_orgid = code_orgstructure.orgid GROUP BY
    code_orgstructure.ext_desc
    </SQL>

    but of course it fail....
    Visit my homepage at http://www.carfield.com.hk

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL group by question

    You need to add all the non-aggregate columns to the GROUP BY clause:

    GROUP BY SELECT code_orgstructure.ext_desc, users.familyname, users.givenname, learningObject.learningid, learningObject.title;

  3. #3
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    13

    Re: SQL group by question

    Originally posted by andrewst
    You need to add all the non-aggregate columns to the GROUP BY clause:

    GROUP BY SELECT code_orgstructure.ext_desc, users.familyname, users.givenname, learningObject.learningid, learningObject.title;
    Thx for reply, however, in my example, what I like to do is select the highest marks among the whole organization, and display the organization name, highest mark, user name and related course name of that highest mark record. How can I do?

    If I include the user name, and course name in group by, then it just show the highest score of that student in that course, which is not what I want... Or I should write something like

    select orgname, max(username), max(course_name), max(score) ..... group by orgname

    ??
    Visit my homepage at http://www.carfield.com.hk

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try:
    PHP Code:
    SELECT
       code_orgstructure
    .ext_desc
    ,  users.givenname
    ,  users.familyname
    ,  learningObject.learningid
    ,  learningObject.title
    ,  AICC_core.highestscore
       FROM transcript transcript_module
       JOIN AICC_core AICC_core
          ON 
    (AICC_core.session_id transcript_module.contentSessionID)
       
    JOIN users users
          ON 
    (users.userid transcript_module.userid)
       
    JOIN code_orgstructure code_orgstructure
          ON 
    (code_orgstructure.orgid users.leaf_orgid)
       
    WHERE  AICC_core.highestscore = (SELECT Max(za.highestscore)
          
    FROM transcript zt
          JOIN AICC_core za
             ON 
    (za.session_id zt.contentSessionID)
          
    JOIN users users
             ON 
    (zu.userid zt.userid)
          
    JOIN code_orgstructure code_orgstructure
             ON 
    (zc.orgid zu.leaf_orgid)
          
    WHERE  zc.ext_desc code_orgstructure.ext_desc)
       
    GROUP BY code_orgstructure.ext_desc 
    to see what that does.

    -PatP

Posting Permissions

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