#1
  1. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,014
    Rep Power
    175

    A few sql questions


    Hi;

    A few questions:

    Here is the table:
    Code:
    CREATE TABLE `members` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `email` varchar(255) NOT NULL,
     `sex` varchar(1) NOT NULL DEFAULT 'm',
     `age` varchar(255) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=472131 DEFAULT CHARSET=latin1
    1 - How can I get the number of each age group? Would this be correct?
    Code:
     SELECT Count(age) AS how_many,
           age
    FROM   `members`
    GROUP  BY age
    2 - How many male, how many female? Is this correct?
    Code:
     SELECT Count(sex) AS how_many,
           sex
    FROM   `members`
    GROUP  BY sex
    3 - How many male and how many female for each age group? I am not sure how to do this one since I can't GROUP by 2 columns.

    Please note that age column is varchar. For example "+30" or "18-22"

    Thanks for the help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,663
    Rep Power
    4288
    1. what happened when you tested this? ™

    2. what happened when you tested this? ™

    3. yes, you can group by more than one column

    Comments on this post

    • requinix agrees : ha, trademark
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,014
    Rep Power
    175
    Rudy all you do is tease tease tease. You just want me on my knees?

    Originally Posted by r937
    1. what happened when you tested this? ™
    Looks right but I am not sure because I am dealing with large data.
    Originally Posted by r937
    2. what happened when you tested this? ™
    Same as 1.
    Originally Posted by r937
    3. yes, you can group by more than one column
    How does it look? I honestly can't really tell from the results easily.
    Code:
     SELECT sex,
           age,
           Count(age) AS C
    FROM   breakup_members
    GROUP  BY sex,
              age
    ORDER  BY c
    LIMIT  0, 30
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,663
    Rep Power
    4288
    good... all good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,014
    Rep Power
    175
    Originally Posted by r937
    good... all good


    Thanks man. Hope you'll have good new year with family. See ya

IMN logo majestic logo threadwatch logo seochat tools logo