SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Kansas City, MO
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to do multiple select sum on same table with different WHERE's

    Okay, hopefull that didn't sound confusing ...

    What I'm trying to do is go through a log file and sum certain columns of data where certain WHERE attributes exist.

    The table is called "game_stats" and the columns I am am using are:

    id
    game_date
    player
    hits
    homeruns
    rbi
    sb
    ab

    And I'm trying to perform the following in a single SQL query

    - Trying to SUM the number of hits a given player had when he had over 4 ab

    Code:
    SELECT sum(hits) AS total_hits FROM game_stats WHERE player='bob smith' && ab>4
    - Then also sum (for the same player) how many rbi the player had when they had over 2 hits

    Code:
    SELECT sum(rbi) AS total_rbi FROM game_stats WHERE player='bob smith' && hits>2
    - And finally sum up the number of homeruns a player had when they had at least 1 ab

    Code:
    SELECT sum(homeruns) AS total_homeruns FROM game_stats WHERE player='bob smith' && ab>=1
    My problem is trying to put all of these into a single MySQL Query ... any suggestions?

    I know this probably seems pretty basic, but my brain decided not to work this morning.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select sum(case when ab > 4 then hits else 0 end) as total_hits,
             sum(case when hits > 2 then rbi else 0 end) as total_rbi,
             sum(case when ab >= 1 then homeruns else 0 end) as total_homeruns
      from game_stats 
     where player = 'bob smith'
        and (ab >= 1
          or hits > 2)

  3. #3
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Kansas City, MO
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the quick response and answer. Works perfect. I knew it was something like that ... but my brain didn't want to back me up on it.

    Thanks again!


Bookmarks

Posting Permissions

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