Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    5

    Exclamation Unanswered: SUM values from two columns in same table

    Okay guys I am trying to put something together and hit a major wall.

    I have the following table...

    content
    -------------------------------------------------------------------------
    --id-----away----away_score----home----home_score----week-----------
    -------------------------------------------------------------------------
    --1-----gaints---------5---------cubs---------2-----------1-------------
    --2-----cubs----------5---------giants--------2-----------1-------------
    --3-----warriors-------5---------cubs---------2-----------1-------------
    --4-----texas---------5---------jays---------2-----------1--------------
    --5-----jays----------5---------cubs---------2-----------1--------------
    --5-----jays----------5---------cubs---------2-----------2--------------

    What I am trying to do is write a query that will give me the total of each team in week 1, ie based on above

    cubs 11
    giants 7
    warriors 5
    texas 5
    jays 7

    I'm not even sure if it is possible but thought I would ask? I guess what makes it so hard is the fact that the team name appears in two different columns.

    If it were possible then my next question is could it be dynamic in nature meaning the results from the array could be placed right into the <? echo $rows['$team']; ?> for each team and each score.

    I'm really new and have gotten so far with this thinking this was going to be the easy part, well shame on me.

    Thanks in advance.

    Please let me know if I can do more to explain better I really need this help want to see this little test project through to completion.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT team
         , SUM(team_score) AS total_score
      FROM ( SELECT away       AS team
                  , away_score AS team_score
               FROM daTable
              WHERE week = 1
             UNION ALL
             SELECT home       AS team
                  , home_score AS team_score
               FROM daTable
              WHERE week = 1
           ) AS u
    GROUP
        BY team
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    5
    I receive this error, also thanks for the fast response!

    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/bhvdsaj/public_html/pslff.com/baseball/junk.php on line 27

    Here is the code I have-

    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");

    $sql=("SELECT team
    ,SUM(team_score) AS total_score
    FROM (SELECT away AS team
    ,away_score AS team_score
    FROM $tbl_name
    WHERE week=1
    UNION ALL
    SELECT home AS team
    ,home_score AS team_score
    FROM $tbl_name
    WHERE week=1 )
    AS u GROUP BY team")or die(mysql_error());

    $result=mysql_query($sql);

    while($rows=mysql_fetch_array($result)){
    ?>

    <td><?php echo $rows["team"];?></td><br />

    <?
    }
    ?>

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't do php, but i'm guessing you forgot to initialize the $tbl_name variable

    please test the query outside of php, i.e. directly in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When in doubt, RTFM: PHP: mysql_fetch_array - Manual

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2012
    Posts
    5
    You guys are the best...I got everything to display as need using PHP.

    One question maybe you can point me in the right direction I need to be able to post the results of the array in different locations such as a predesigned team is there a way to assign unique id's to each row that can be echo'd out.

    Maybe with IF or ELSE statements?

    Thanks again.

    Here is the code I used-

    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");

    $sql=("SELECT team
    ,SUM(team_score) AS total_score
    FROM (SELECT away AS team
    ,score_away AS team_score
    FROM $tbl_name
    WHERE week='1'
    UNION ALL
    SELECT home AS team
    ,score_home AS team_score
    FROM $tbl_name
    WHERE week='1' )
    AS u GROUP BY team")or die(mysql_error());

    $result=mysql_query($sql);

    while($rows=mysql_fetch_array($result)){

    ?>


    <td><?php echo $rows["team"];?></td><br /><td><?php echo $rows["total_score"];?></td><br />

    <?
    }
    ?>

  7. #7
    Join Date
    May 2012
    Posts
    5
    This might even be easier...is there a way to pull the column 'tid' from a different table (call it 'teams') so that when the results from our query display it will display the 'tid' that corresponds with the away or home fields from the first table.

    right now I get this-

    Angels 12
    Astros 15
    Athletics 24
    Blue Jays 15
    Braves 22
    Brewers 18
    etc....

    I am looking for this-

    1 Angels 12
    2 Astros 15
    3 Athletics 24
    4 Blue Jays 15
    5 Braves 22
    6 Brewers 18
    etc...

    With the 1,2,3,4,5,6 being the "tid FROM table('teams')

    Thanks again guys.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TryingHard View Post
    ...is there a way to pull the column 'tid' from a different table (call it 'teams')
    yes, i suppose there is

    but it prompts the question of why you populated the scores table with team names and not their ids
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2012
    Posts
    5
    I guess I wasnt thinking far enough in advance before...

    The id's have to come from another table (I think) because the id's from the table 'content' that I am using to put together the total_scores are for each row in the table and they are not assign to a specific team.

    Hope that makes sense?

    Thank again.

Posting Permissions

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