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,089
    Thanks
    51
    Thanked 506 Times in 493 Posts
    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.
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • Users who have thanked tangoforce for this post:

    maici (06-29-2013)

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,089
    Thanks
    51
    Thanked 506 Times in 493 Posts
    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)
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • 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,089
    Thanks
    51
    Thanked 506 Times in 493 Posts
    You're welcome
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!


  •  

    Posting Permissions

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