SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with SUM

    Hi all!

    I have a problem with SUMs. In my table I have tens of Hs and ABs fields associated with playerID field. I need to get SUM of Hs divided by SUM of ABs and order the results by the derived number. When I use the below query it shows SUM.

    Code:
        SELECT SUM(H)
         , SUM(AB)
         , playerID 
          FROM mytable 
         WHERE yearID = 2005 
      GROUP BY playerID 
         LIMIT 10
    This returns me SUM of all the H associated by playerID. But when I change the code to below one, it does not give me SUM.

    Code:
        SELECT SUM(H)
                 , SUM(AB)
                 , SUM(H)/SUM(AB) HAB
                 , playerID 
          FROM mytable 
         WHERE yearID = 2005 
    GROUP BY playerID 
    ORDER BY HAB DESC 
          LIMIT 10
    H and AB fields are both integers. I hope I was able to clearly explain what my problem is.

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by bamAZy
    But when I change the code to below one, it does not give me SUM.
    it doesn't? well, it looks okay to me

    what does it give you, if not the sum?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It gives less than its sum. I guess it takes one H and one AB instead of SUM(H) and SUM(AB).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    can you come up with some sample rows to demonstrate this please
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT SUM( H ) h, SUM( AB ) , playerID
    FROM  mytable 
    WHERE yearID = 2005
    GROUP  BY playerID
    ORDER  BY h DESC
    produces:

    h SUM( AB ) playerID
    221 668 15664
    206 679 13869
    202 654 6952
    199 594 8083
    199 654 14037
    198 613 16138
    197 624 3227
    196 677 12172
    195 591 11499
    194 605 12101
    194 644 15940


    Code:
    SELECT SUM( H ) h, SUM( AB ) ab, SUM( H )  / SUM( AB ) hab, playerID
    FROM  mytable 
    WHERE yearID = 2005
    GROUP  BY playerID
    ORDER  BY hab DESC
    gives:

    h ab hab playerID
    0 2 0.00 16408
    0 2 0.00 16415
    0 1 0.00 16419
    0 3 0.00 16422
    0 1 0.00 16424
    0 15 0.00 16425
    0 4 0.00 16426
    0 4 0.00 16432

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    H and AB fields are both integers
    so if SUM(H) is less than SUM(AB), the answer is zero -- it has to be

    these are batting averages, right?

    if you want a decimal value, use

    ... 1.000*SUM(H)/SUM(AB) as hab

    not sure if this will give you more than three decimal places, but you could always use ROUND or CAST as DECIMAL(4,3) to make sure
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,
    you are right, these are Batting Averages.

    I still have problem that I can not define.

    Code:
    SELECT SUM( H ) h, SUM( AB ) ab, 1.00 * SUM( H )  / SUM( AB ) hab, playerID
    FROM  mytable 
    WHERE yearID = 2005
    GROUP  BY playerID
    ORDER  BY h DESC 
    LIMIT 10

    This code produces

    h ab hab playerID
    221 668 0.3308 15664
    206 679 0.3034 13869
    202 654 0.3089 6952
    199 594 0.3350 8083
    199 654 0.3043 14037
    198 613 0.3230 16138
    197 624 0.3157 3227
    196 677 0.2895 12172
    195 591 0.3299 11499
    194 605 0.3207 12101

    But I want them to be ordered by HAB. But when I try to do so I get NULLs and zeros.

    Code:
    SELECT SUM( H ) h, SUM( AB ) ab, 1.00 * SUM( H )  / SUM( AB ) hab, playerID
    FROM  mytable 
    WHERE yearID = 2005
    GROUP  BY playerID
    ORDER  BY hab DESC 
    LIMIT 10
    The result is

    h ab hab playerID
    0 0 NULL 70
    0 0 NULL 142
    0 0 NULL 189
    0 0 NULL 190
    0 0 NULL 247
    0 0 NULL 267
    0 0 NULL 432
    0 0 NULL 459
    0 0 NULL 732
    0 0 NULL 749

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, that seems to suggest that there are some players with zero at bats, right?

    you can filter these out with a WHERE condition

    they would not show up in a "top ten batting averages" listing anyway
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I still can't get the result I want to get. I already write "ORDER BY hab DESC" and it's supposed to order by hab but it gives me NULLS. Is NULL greater than any integer? Why does it give me NULL when I order by hab, whereas it gives decimal value when I order by h. I still haven't found out how to get them in descending order

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    still getting NULLs? did you change the WHERE clause? can i see your query?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, I can't figure out how to change WHERE clause It says "Unknown column 'hab' in 'where clause'" when I write "WHERE hab <> NULL" and "Invalid use of group function" when I write "WHERE SUM(ab) <> NULL". How should I change WHERE clause?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    can i see your query?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     SELECT SUM( H ) h, SUM( AB ) ab, 1.00 * SUM( H ) / SUM( AB ) hab, playerID
    FROM mytable
    WHERE yearID = 2005 AND SUM( ab ) <> NULL
    GROUP BY playerID
    ORDER BY hab DESC
    LIMIT 10
    gives "Invalid use of group function."

    Code:
     SELECT SUM( H ) h, SUM( AB ) ab, 1.00 * SUM( H ) / SUM( AB ) hab, playerID
    FROM mytable
    WHERE yearID = 2005 AND hab <> NULL
    GROUP BY playerID
    ORDER BY hab DESC
    LIMIT 10
    gives "Unknown column 'hab' in 'where clause'".

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    either filter out each game where AB=0

    SELECT SUM( H ) h, SUM( AB ) ab, 1.00 * SUM( H ) / SUM( AB ) hab, playerID
    FROM mytable
    WHERE yearID = 2005 AND ab > 0
    GROUP BY playerID
    ORDER BY hab DESC
    LIMIT 10

    or filter out the players with SUM(AB) = 0

    SELECT SUM( H ) h, SUM( AB ) ab, 1.00 * SUM( H ) / SUM( AB ) hab, playerID
    FROM mytable
    WHERE yearID = 2005
    GROUP BY playerID
    HAVING SUM( ab ) =0
    ORDER BY hab DESC
    LIMIT 10
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first query gave me

    h ab hab playerID
    1 1 1.0000 990
    1 1 1.0000 11341
    1 1 1.0000 15412
    1 1 1.0000 15777
    1 1 1.0000 16221
    1 1 1.0000 16668
    3 4 0.7500 15771
    2 3 0.6667 3826
    2 3 0.6667 9708
    1 2 0.5000 1671


    And the second gives:

    h ab hab playerID
    0 0 NULL 70
    0 0 NULL 142
    0 0 NULL 189
    0 0 NULL 190
    0 0 NULL 247
    0 0 NULL 267
    0 0 NULL 432
    0 0 NULL 459
    0 0 NULL 732
    0 0 NULL 749

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    aaargh, my bad

    the second query should say

    HAVING SUM(ab) > 0

    but you shoulda seen that too, eh
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, I'm grateful for your support However, I still don't get the desired result

    Code:
    SELECT SUM( H ) h, SUM( AB ) ab, 1.00 * SUM( H ) / SUM( AB ) hab, playerID
    FROM mytable
    WHERE yearID = 2005
    GROUP BY playerID
    HAVING SUM( ab ) > 0
    ORDER BY hab DESC
    LIMIT 10
    returns

    h ab hab playerID
    1 1 1.0000 990
    1 1 1.0000 11341
    1 1 1.0000 15412
    1 1 1.0000 15777
    1 1 1.0000 16221
    1 1 1.0000 16668
    3 4 0.7500 15771
    2 3 0.6667 3826
    2 3 0.6667 9708
    1 2 0.5000 1671

    What I want is the below results ordered by hab

    h ab hab playerID
    221 668 0.3308 15664
    206 679 0.3034 13869
    202 654 0.3089 6952
    199 594 0.3350 8083
    199 654 0.3043 14037
    198 613 0.3230 16138
    197 624 0.3157 3227
    196 677 0.2895 12172
    195 591 0.3299 11499
    194 605 0.3207 12101

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the results are ordered by hab

    all those guys that are 1-for-1 with a batting average of 1.000 would have to be at the top of the list, right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Baku, Azerbaijan
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stıpid me

    You are right, they have to be at the top of the list

    To get my desired result, I ordered by sum of h and put HAVING SUM(H) > 192 and it worked.

    Rudy, thanks a lot for the help! I'm more than grateful!


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
  •