Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    PHP - mysql Count same word column values

    I need to count follow thing

    this is "teams" table
    id - team - points
    1 - Carteam - 0
    2 - Truckers - 0

    and this is "users" table
    id - driver - team - aus - mal - chi
    1 - Joey - Carteam - 3 - 3 - 3
    2 - Eric - Carteam - 3 - 3 - 5
    3 - John - Carteam - 2 - 5 - 5

    I need to get count users points in same team together
    to get team total scores. That "points" in teams table is useless?

  • #2
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I got this so far

    PHP Code:
    select 
    `id`,
    `
    driver`, 
    `
    team`, 
    `
    aus
    + `
    mal
    + `
    chi
     + `
    bah`
      + `
    spa`
       + `
    mon`
        + `
    can`
         + `
    eur`
          + `
    grb`
           + `
    ger`
            + `
    hun`
             + `
    bel`
              + `
    ita`
               + `
    sin`
                + `
    jap`
                 + `
    kor`
                  + `
    ind`
                   + `
    abd`
                    + `
    uns`
                     + `
    bra`
    as 
    Total from users ORDER BY Total DESC); 

  • #3
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,313
    Thanks
    58
    Thanked 525 Times in 512 Posts
    Blog Entries
    5
    See next post instead - this one is left in for continuity.

    as Total from users where `team` = '<team_name>' ORDER BY Total DESC

    You will also need to add sum() into your columns that you're selecting too:

    select `id`,`driver`, `team`, sum(`aus` + `mal` + `chi` + `bah`) as Total from users where `team` = '<team>' ORDER BY Total DESC;

    Tested on a test table in phpmyadmin but you'll need to adjust it for your use.
    Last edited by tangoforce; 06-29-2013 at 12:03 PM.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • Users who have thanked tangoforce for this post:

    maici (06-29-2013)

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,313
    Thanks
    58
    Thanked 525 Times in 512 Posts
    Blog Entries
    5
    Nevermind.. ignore my last. I see you're wanting the totals for each team not for individual teams - doh!

    This should do it for you:

    PHP Code:
    select 
    `id`,
    `
    driver`, 
    `
    team`, 
    sum(`aus
    + `
    mal
    + `
    chi
     + `
    bah`
      + `
    spa`
       + `
    mon`
        + `
    can`
         + `
    eur`
          + `
    grb`
           + `
    ger`
            + `
    hun`
             + `
    bel`
              + `
    ita`
               + `
    sin`
                + `
    jap`
                 + `
    kor`
                  + `
    ind`
                   + `
    abd`
                    + `
    uns`
                     + `
    bra`)
    as 
    Total from users group by `teamorder by Total DESC); 
    Note the group by and order by. These go in alpabetical order so group is used before order (well thats how I remember it anyway lol)
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • Users who have thanked tangoforce for this post:

    maici (06-29-2013)

  • #5
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tangoforce View Post
    Nevermind.. ignore my last. I see you're wanting the totals for each team not for individual teams - doh!

    This should do it for you:

    PHP Code:
    select 
    `id`,
    `
    driver`, 
    `
    team`, 
    sum(`aus
    + `
    mal
    + `
    chi
     + `
    bah`
      + `
    spa`
       + `
    mon`
        + `
    can`
         + `
    eur`
          + `
    grb`
           + `
    ger`
            + `
    hun`
             + `
    bel`
              + `
    ita`
               + `
    sin`
                + `
    jap`
                 + `
    kor`
                  + `
    ind`
                   + `
    abd`
                    + `
    uns`
                     + `
    bra`)
    as 
    Total from users group by `teamorder by Total DESC); 
    Note the group by and order by. These go in alpabetical order so group is used before order (well thats how I remember it anyway lol)

    Thank you very much I feel like I have to pay you ))

  • #6
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,313
    Thanks
    58
    Thanked 525 Times in 512 Posts
    Blog Entries
    5
    You're welcome
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!


  •  

    Posting Permissions

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