Problem with SUM

[bamAZy]

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.

    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.

    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.

[r937]

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?

[bamAZy]

It gives less than its sum. I guess it takes one H and one AB instead of SUM(H) and SUM(AB).

[r937]

can you come up with some sample rows to demonstrate this please

[bamAZy]

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

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

[r937]

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

[bamAZy]

Rudy,
you are right, these are Batting Averages.

I still have problem that I can not define.

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.

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

[r937]

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

[bamAZy]

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

[r937]

still getting NULLs? did you change the WHERE clause? can i see your query?

[bamAZy]

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?

[r937]

can i see your query?

[bamAZy]

 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."

 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'".

[r937]

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

[bamAZy]

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

[r937]

aaargh, my bad

the second query should say

HAVING SUM(ab) > 0

but you shoulda seen that too, eh

[bamAZy]

Rudy, I'm grateful for your support However, I still don't get the desired result

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

[r937]

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?

[bamAZy]

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!