Loading ...
Sorry, an error occurred while loading the content.

Re: [baseball-databank] Another SQL query request

Expand Messages
  • Zach
    Message 1 of 3 , Jul 27, 2004
    • 0 Attachment
      SELECT count( * ) AS W
      FROM mlballgames2
      WHERE hometeam = 'BOS' AND homescore - awayscore =1
      UNION
      SELECT count( * ) AS L
      FROM mlballgames2
      WHERE hometeam = 'BOS' AND awayscore - homescore =1
       
       
      (did edi the names to match the db I got with that stuff)
       
      which gave me a shock, damn Boston has been good in one run games historically
       
      1432-851
       
      Dont forget you would actually want to check on forfeits and all that jazz also.
       
      That is also still technically two selects, but only one result set - so if you are using PHP - it would be just one sql statement and one mysql_result_array thingy, but literally all thats different is the UNION in between them.
       
      Beyond that, I assume you are trying to make it more efficient. I would ask, is it more efficient to spend time looking for a way to be more effiient on something that takes very few seconds to do to being with, or is it more efficient to let it take its sweat ole time. 
       
      If you are using it real world, and want it to be avaible real time, fake it (its not hard, I can get you my girlfriends cell number for pointers, shes good at it) - - and seriously - if you are using it real time, dont - rip the data out, save it as the html files you would present to people, update them daily if need be - thats efficient, not having real time data queries through that much data.  If you still need more dynamic stuff avaible after you get the numbers, just rip the numbers and save them in a useable form for yourself -
       
      Databases like these should be used to build files, that present information  to people, not to serve files that present information to people.
       
       
       
       
       
       
       
      Now the what I would do part - I am a php guy, I know it, I can do things with it, sql, and the other junk are just along for my uniformed ride I take em on
       
      If I was going to bother to ask for something from the server, and the servers db - I would probably have to be somewhat polite, and respectful of the little ole db and server, and get it all at once (well this doenst get it all, but I would grab more if I was using it for real word purpose)
       
       
      All 4505 3502
      1 1432 851
      2 761 719
      New York All 473 488
      New York 1 144 113
      New York 2 83 94
       
       
      Thats my outcome - will post it up and paste a url here for the source - in translation - you can see the 1432-851 matches the number above - all, is all (remember home only)
      1 is one run
      2 is two run
      New York All - Is all the games between the Yankkes (amazingly enough, if you ddnt get that from the cryptic description) and the Boston Red Sox
      1,2 etc above and above
       
       
      I cant help it, I am a lazy little sob.  If I am going to do it, why not just do it all and be done with, especially since that is one query, and is probably faster than the two select method.
       
      The results have the won / loss against each team, all of them, and the won / loss against each team, all of them, by each margin, all of them ,that they have had in history (so 1 run games, 20 run games, etc)
       
      Also has just the home against everybody, same thing.
       
      Probably since I wrote already, and it would be nice on my site, I will pretty it up into some nice something and publish it on one of my websites, and will pass on the url here if so desired, though doubt it would be today, since it tis football season (as far as my work load is concerned)
       
       
      <?
      $con= mysql_connect("localhost","uuuuuu","pppppp");
      $dbx = mysql_select_db("dbdbdbdbdb", $con);
      $sq="SELECT homescore,awayscore,awayteam FROM mlballgames2 WHERE hometeam = 'BOS'";
      $sqlq = mysql_query($sq, $con);
      while ($ll = mysql_fetch_array($sqlq)) {
      $h=$ll["homescore"];
      $a=$ll["awayscore"];
      $at=$ll["awayteam"];
      if ($h>$a) {
      $w++;
      $d=$h-$a;
      $wtr="wx".$d;
      $$wtr++;
      $wt="wx".$at;
      $$wt++;
      $wtt="wx".$at.$d;
      $$wtt++;
      } else {
      $l++;
      $d=$a-$h;
      $ltr="lx".$d;
      $$ltr++;
      $lt="lx".$at;
      $$lt++;
      $ltt="lx".$at.$d;
      $$ltt++;
      }
      }
      echo "<br>";
      echo "All ".$w." ".$l;
      echo "<br>";
      echo "1 ".$wx1." ".$lx1;
      echo "<br>";
      echo "2 ".$wx2." ".$lx2;
      echo "<br>";
      echo "New York All ".$wxNYA." ".$lxNYA;
      echo "<br>";
      echo "New York 1 ".$wxNYA1." ".$lxNYA1;
      echo "<br>";
      echo "New York 2 ".$wxNYA2." ".$lxNYA2;
      echo "<br>";
      ?>
       
       
       
       
       
       
       
       
       
       
       
      ----- Original Message -----
      From: "walshj58" <john.walsh@...>
      To: baseball-databank@yahoogroups.com
      Sent: Tuesday, July 27, 2004 06:55 AM
      Subject: [baseball-databank] Another SQL query request

      Ok, getting the W-L record for a team in 1-run games from the game log
      files. I've created a big table of game data, 1 row for each game. I
      know how to get the number of W with 1 query and the number of L with
      a second query (schematically):

      select count(*) as W from game_logs where home_tm='BOS' and
      home_score-vis_score=1;

      select count(*) as L from game_logs where home_tm='BOS' and
      vis_score-home_score=1;

      This gives me the W and L for 1 runs games for Boston at home. Is
      there a way I can get the same results from a single query?

      This same problem comes up all the time when processing the game log
      or pbp data, so I'd appreciate a solution.

      thanks in advance,

      John






      http://www.baseball-databank.org/








      Yahoo! Groups Sponsor


      ADVERTISEMENT












      Yahoo! Groups Links

      To visit your group on the web, go to:http://groups.yahoo.com/group/baseball-databank/&nbsp;
      To unsubscribe from this group, send an email to:baseball-databank-unsubscribe@yahoogroups.com&nbsp;
      Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
    • Tangotiger
      This is handled using the Michael Mavrogiannis technique of using the IIF function in MS Access. Please do a search in the archives for IIF, and select the
      Message 2 of 3 , Jul 28, 2004
      • 0 Attachment
        This is handled using the Michael Mavrogiannis
        technique of using the IIF function in MS Access.
        Please do a search in the archives for IIF, and select
        the post written by mmvaro.

        Tom
        --- walshj58 <john.walsh@...> wrote:

        > Ok, getting the W-L record for a team in 1-run games
        > from the game log
        > files. I've created a big table of game data, 1 row
        > for each game. I
        > know how to get the number of W with 1 query and the
        > number of L with
        > a second query (schematically):
        >
        > select count(*) as W from game_logs where
        > home_tm='BOS' and
        > home_score-vis_score=1;
        >
        > select count(*) as L from game_logs where
        > home_tm='BOS' and
        > vis_score-home_score=1;
        >
        > This gives me the W and L for 1 runs games for
        > Boston at home. Is
        > there a way I can get the same results from a single
        > query?
        >
        > This same problem comes up all the time when
        > processing the game log
        > or pbp data, so I'd appreciate a solution.
        >
        > thanks in advance,
        >
        > John
        >
        >
        >





        __________________________________
        Do you Yahoo!?
        New and Improved Yahoo! Mail - 100MB free storage!
        http://promotions.yahoo.com/new_mail
      Your message has been successfully submitted and would be delivered to recipients shortly.