Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: group by problem

    hi,
    could anyone take a look at following problem?


    i have created two tables

    mysql> show create table students;
    +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | students | CREATE TABLE `students` (
    `Studid` int(11) default NULL,
    `firstname` varchar(20) default NULL,
    `lastname` varchar(20) default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
    +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> show create table grade;
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | grade | CREATE TABLE `grade` (
    `gradeid` int(11) default NULL,
    `Studid` int(11) default NULL,
    `grade` int(11) default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    then i loaded data into tabes from following files

    csxia-desktop:/data/learning/mysql/load # cat stduent
    1 Bill Smith
    10 guifen xia
    4 chengshun xia
    2 Bobby Brown
    3 Derek Zoolander
    csxia-desktop:/data/learning/mysql/load # cat grade
    1 2 61
    2 3 98
    3 1 87
    4 10 90
    5 10 81
    6 4 78
    7 4 90
    8 1 50
    9 2 23
    10 3 100
    11 10 90
    12 4 40
    18 1 58
    19 2 82
    20 3 68
    csxia-desktop:/data/learning/mysql/load #


    what i want to do is use query to list all grade for all stduents


    query #1: it works fine

    mysql> select S.Studid,S.firstname,S.lastname,G.grade from students S inner join grade G on S.Studid = G.Studid order by S.Studid,G.grade DESC;+--------+-----------+-----------+-------+
    | Studid | firstname | lastname | grade |
    +--------+-----------+-----------+-------+
    | 1 | Bill | Smith | 87 |
    | 1 | Bill | Smith | 58 |
    | 1 | Bill | Smith | 50 |
    | 2 | Bobby | Brown | 82 |
    | 2 | Bobby | Brown | 61 |
    | 2 | Bobby | Brown | 23 |
    | 3 | Derek | Zoolander | 100 |
    | 3 | Derek | Zoolander | 98 |
    | 3 | Derek | Zoolander | 68 |
    | 4 | chengshun | xia | 90 |
    | 4 | chengshun | xia | 78 |
    | 4 | chengshun | xia | 40 |
    | 10 | guifen | xia | 90 |
    | 10 | guifen | xia | 90 |
    | 10 | guifen | xia | 81 |
    +--------+-----------+-----------+-------+
    15 rows in set (0.00 sec)

    mysql>

    and i use query #2 while list only one rows for each stduents

    mysql> select S.Studid,S.firstname,S.lastname,G.grade from students S inner join grade G on S.Studid = G.Studid group by S.Studid order by S.Studid,G.grade DESC;
    +--------+-----------+-----------+-------+
    | Studid | firstname | lastname | grade |
    +--------+-----------+-----------+-------+
    | 1 | Bill | Smith | 87 |
    | 2 | Bobby | Brown | 61 |
    | 3 | Derek | Zoolander | 98 |
    | 4 | chengshun | xia | 78 |
    | 10 | guifen | xia | 90 |
    +--------+-----------+-----------+-------+
    5 rows in set (0.00 sec)

    mysql>


    could anyone point out what is the wrong points and what is the difference between query 1 and query 2??


    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Chengshun Xia View Post
    could anyone point out what is the wrong points and what is the difference between query 1 and query 2??
    well, the difference should be pretty obvious, query 1 lists all grades for each student, but query 2 lists only one grade per student

    as for whether that's wrong or not, that all depends on which grade for each student you wanted to see

    since you use a GROUP BY clause on the student but don't use an aggregate function on the grade, the grade you get for each student is indeterminate

    see GROUP BY and HAVING with Hidden Columns

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    7
    thanks for your quick reply!

Posting Permissions

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