Results 1 to 6 of 6
  1. #1
    Join Date
    May 2014
    Posts
    4

    Unanswered: Linking two id's

    Hello,

    I would be grateful if you could help me with this one problem.

    I have two tables. One named title, other users.

    The table 'users' has column called group_id. The group_id stores numbers from one to four.

    For example:

    group_Id

    1
    4
    3
    1
    2
    2

    The tables 'groups' has a column called title and id. The title holds a string like ( E8-1 - it's name of class in school ) and the id holds a number(it represents which class is which, like in real school there are many clases), from one to four, like in the users table.

    It looks like this:
    id title
    1 I7-1
    2 I7-2
    3 I-6 (2013)
    4 I-5

    group_id represents the number of a student. For example if there are two instances of a number 2(that means, that there are 2 students is going into I7-2 class, because that id has 2 as well).

    What I need to do is get a new table, which counts how many students are going into particular class. How should I do that ? I tried using SUM, GROUP BY, but it didn't work. I am new to databases, so maybe I am missing something.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So you are going to need the count aggregate function, a group by clause, a join statement assuming you want the class description as opposed to just the class id. Something like:-
    Code:
    Select count(users.group_id) as NoInClass, groups.title 
    from users
    join groups on users.group_id = groups.id
    group by groups.id
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Posts
    4
    Quote Originally Posted by healdem View Post
    So you are going to need the count aggregate function, a group by clause, a join statement assuming you want the class description as opposed to just the class id. Something like:-
    Code:
    Select count(users.group_id) as NoInClass, groups.title 
    from users
    join groups on users.group_id = groups.id
    group by groups.id
    It does seem to work, thanks.

    But I don't quite understand which line of the code 'sums up' how many group_id's(students going to the same class) are. It first adds all the group_id - count(users.group_id), and then adds up the same numbers (group by groups.id), so if i have two 2's, then it will be 4?

  4. #4
    Join Date
    May 2014
    Posts
    4
    Thanks, it works.

    Though I don't fully understand the code. What place exactly does the adding up of the same group_id's(students going to the same class)?
    Is it by using count(adding all the group_id's) at first and then grouping up them(adding only the same group_id's) ?

  5. #5
    Join Date
    May 2014
    Posts
    4
    Testing this

    EDIT: Sorry for the spamming a bit, I thought I missed something when replying, because messages didn't pop up, now I see that posts required permission.
    Last edited by Akimb; 05-22-14 at 12:33.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its not adding/summing, its using the count function to COUNT the number of rows GROUPed BY the class/group_id

    look up the count function
    look up the group by keywords

    you could get the same effect by simplifying the query
    Code:
    Select count(users.group_id) as NoInClass, users.group_id
    from users
    group by users.groups_id
    Last edited by healdem; 05-22-14 at 14:15.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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