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

    Unanswered: Question: Trying to add up duplicate rows for a leaderboard

    Okay, I tried to type this once but it didn't go through for some reason..


    Lets say I have a SQL Server with the table name Users
    It has 3 columns Key, ID, Amount


    I want to determine how to add up all the columns with a specific ID, for every ID in the table and then sort them from highest to lowest.

    For Example:
    1 1 20
    2 1 10
    3 2 5
    4 2 10
    5 3 11
    6 3 20

    I want it to output
    User: 3 Amount: 31
    User: 1 Amount: 30
    User: 2 Amount: 15

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Use the SUM predicate and GROUP BY amount
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2014
    Posts
    4
    Awesome, that worked great! Thank you

    Now the only thing I have left to do is add the usernames

    I belive INNER JOIN will work for that.
    Prob something like


    PHP Code:
    <?php

    include('Nav.php');
    include(
    'Header.php');
    $x 0;
    $y 0;

    $SQL_Select_Miles_User "SELECT User_Steps_User_ID, User_Account_First_Name, User_Account_Last_Name, SUM(User_Steps_Miles) AS 'Miles' FROM User_Steps INNER JOIN User_Account ON User_Account.User_Account_ID = User_Steps.User_Steps_User_ID GROUP BY User_Steps.User_Steps_User_ID";        
    $RESULT_Select_Miles_User mysql_query($SQL_Select_Miles_User);

    echo 
    '<h1 class="Header">Leader Board</h1> Note: If a User/Team has 0 miles they/it are not listed <br>';
    echo
    '<br>
         <div id"container">
         <div class="page-wrap2" Style="Text-Align:Left;">
        <table width="100%" align="center" style="text-align:center;">
            <tr style="background-color:#75D1FF;">
                <th>User Name</th>
                <th>Miles</th<
            </tr>'
    ;
                    
    while(
    $User mysql_fetch_assoc($RESULT_Select_Miles_User))
        {
        if(
    $x != 1)
            {
            echo
    '<tr>';
            echo
    '<td>'.$User['User_Account_First_Name'].' '.$User['User_Account_Last_Name'].'</td><td>'.$User['Miles'].'</td>';
            echo
    '</tr>';
            
    $x 1;
            }
        else
            {
            if(
    $x != 0)
            {
            echo
    '<tr style="background-color:#EBD699;">';
            echo
    '<td>'.$User['User_Account_First_Name'].' '.$User['User_Account_Last_Name'].'</td><td>'.$User['Miles'].'</td>';
            echo
    '</tr>';
            
    $x 0;
            }
            }
        }
    echo
    '</table></div>';
         
    $SQL_Select_Miles_Team "SELECT User_Steps_User_ID, Team_Name, SUM(User_Steps_Miles) AS 'Miles' FROM User_Steps INNER JOIN Team_Info ON Team_Info.Team_Info_Key = User_Steps.User_Steps_Team_ID GROUP BY User_Steps.User_Steps_Team_ID";        
    $RESULT_Select_Miles_Team mysql_query($SQL_Select_Miles_Team);

    echo
    '<div class="page-wrap3" Style="Text-Align:Left;">
        <table width="100%" align="center" style="text-align:center;">
            <tr style="background-color:#75D1FF;">
                <th>Team Name</th>
                <th>Miles</th<
            </tr>'
    ;
                    
    while(
    $Team mysql_fetch_assoc($RESULT_Select_Miles_Team))
        {
        if(
    $x != 1)
            {
            echo
    '<tr>';
            echo
    '<td>'.$Team['Team_Name'].'</td><td>'.$Team['Miles'].'</td>';
            echo
    '</tr>';
            
    $x 1;
            }
        else
            {
            if(
    $x != 0)
            {
            echo
    '<tr style="background-color:#EBD699;">';
            echo
    '<td>'.$Team['Team_Name'].'</td><td>'.$Team['Miles'].'</td>';
            echo
    '</tr>';
            
    $x 0;
            }
            }
        }
    echo
    '</table></div>
         <div class"clear"></div>
         </div>
         <br><br><br><br> <br><br><br><br>'
    ;
                
                
    include(
    'Footer.php');
    ?>
    Last edited by Jason0885; 01-24-14 at 12:04.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As your current oroblem is the sql why do you feel the need to post god knows how much php stuff.
    Id syggest you develop your query within a suitable query browser. If you dont have one consider something like HeidiSQL
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2014
    Posts
    4
    Well at this moment,

    it is only pulling the Username name, The user's steps. and team's steps
    It pulls the username to display
    IT pulls the team name to display
    it pulls their steps to add the together


    It also uses the user ID, and Team ID to add up the steps
    It uses the user id to find all the listings in the table that have that user ID
    It does the same thing with the team ID


    The user leaderboard, displays UserName - Total Steps
    The Team leaderboard displays Team Name - Total Steps

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Very vague pseudo code for you to play with
    Code:
    SELECT scores_or_whatever.some
         , scores_or_whatever.stuff
         , users.username
    FROM   users
     INNER
      JOIN (
            <insert your grouped query here>
           ) As scores_or_whatever
        ON scores_or_whatever.matching_field = users.matching_field
    George
    Home | Blog

Posting Permissions

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