calculation problem 2

[captlid]

SELECT team, w, l, sum( w / ( w + l ) ) AS pct, MAX( w - l ) - ( w - l ) / 2 AS gb
FROM standings
GROUP BY team
ORDER BY w DESC

I am wondering why its calculating wrong.
max(4-0) - (1-3)/2
MAX( w - l ) - ( w - l ) / 2

Mathematically the above should equal 3, its outputting -1
(4-0) - (1-3)/2 = 3

Is it the equation, or the query?

thanks,

 

[r937]

you can't show w and l at the same time as sum(w-l)

the group by is wrong

what do you actually want to show, each game? or calculations for all games?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

I wanted to calculate the games behind value for each team

 

[r937]

so, games behind, you would see exactly one row per team, right?

so you cannot show w and l on each lline, because clearly, each team has multiple w's and l's

what version of mysql are you on? you need at least one subquery or derived table to do games behind

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

I am using 4.012 and my hosting provider is also using something below 4.1

The table I am using is setup like
w l pf pa
4 0 20 11
1 3 10 17

The percentage calculates normally.

 

[r937]

okay, i think i misunderstood

your table also has a tema name i assume?

and there is already only one row per team, right?

so sum() of anything is wrong


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

i am confused then,
this calculates properly......
sum( w / ( w + l ) ) AS pct

 

[r937]

sum is a column aggregate function, it calculates the sum of a column of numbers

you have only one row per team, right?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

yes i only have one row per team

 

[captlid]

team w l pf pa
uno 4 0 20 11
dos 1 3 10 17

 

[captlid]

so is what i am trying to do impossible without subqueries?

 

[r937]

sample data --

insert into standings values
( 'uno', 4, 0, 20, 11 )
,( 'dos', 1, 3, 10, 17 )
,( 'foo', 5, 0, 22, 9 )
,( 'bar', 5, 1, 21, 10 )
,( 'qux', 4, 1, 20, 5 )
,( 'fap', 3, 3, 11, 11 )
;

select t1.team
     , t1.w
     , t1.l
     , t1.pf
     , t1.pa
     , t2.team as t2team
     , t2.w    as t2w
     , t2.l    as t2l
     , t2.pf   as t2pf
     , t2.pa   as t2pa
     , 100.000 * t1.w / (t1.w + t1.l) as pct
     , ( t2.w - t1.w ) + ( t1.l - t2.l ) as gb
  from standings as t1
cross
  join standings as t2
cross
  join standings as t3  
group
    by t1.team
     , t1.w
     , t1.l
     , t1.pf
     , t1.pa
     , t2team
     , t2w
     , t2l
     , t2pf
     , t2pa
having t2w-t2l
     = max(t3.w-t3.l)     
order
    by pct desc
     , t1.w-t1.l desc
     , gb
     , t1.pf-t1.pa desc

you need to keep the t2 columns in both the SELECT and the GROUP BY

however, when you go to display your data, you would simply not show the t2 columns

sample results (not showing t2 columns) --

team w l pf pa pct gb
foo 5 0 22 9 100 0
uno 4 0 20 11 100 1
bar 5 1 21 10 83.3 1
qux 4 1 20 5 80 2
fap 3 3 11 11 50 5
dos 1 3 10 17 25 7

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

thank you very much. From what I understand you joined the table to itself 3 times. Also if I understand correctly a cross join allows you to have a result all in one row. right?

I still am confused why a third table is needed.

 

[r937]

you need to join the table to itself in order to have each team together with the team in first place in order to do the "games back" calculation

you need the third table to do the HAVING clause to force the 2nd table to be the team in first place

you could eliminate the 3rd table with a subquery (requires 4.1)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)