SEO

#1 (**permalink**) 02-28-2008, 07:36 AM

I'm attempting to perform a calculation at the query level and have run into
the following problem.

I would like to calculate a percentage using the SUM totals of two other
values. I assume these need to be assigned to variables, but am unaware of
the correct syntax. Can someone clue me in? Thanks.

SELECT mr.teamid,
SUM( IF( mr.winner = '1', 1, 0 ) ) AS wins,
SUM( IF( mr.winner = '0', 1, 0 ) ) AS losses,
SUM(wins/losses) AS percentage <----- how to correctly calculate
FROM match_result mr
GROUP BY mr.teamid

#2 (**permalink**) 02-28-2008, 07:36 AM

Bosconian wrote:
> SELECT mr.teamid,
> SUM( IF( mr.winner = '1', 1, 0 ) ) AS wins,
> SUM( IF( mr.winner = '0', 1, 0 ) ) AS losses,
> SUM(wins/losses) AS percentage <----- how to correctly calculate
> FROM match_result mr
> GROUP BY mr.teamid

Would this do what you want?

SELECT mr.teamid,
SUM( IF( mr.winner = '1', 1, 0 ) ) / COUNT(mr.winner) AS wins_pct,
SUM( IF( mr.winner = '0', 1, 0 ) ) / COUNT(mr.winner) AS losses_pct,
FROM match_result mr
GROUP BY mr.teamid

Unfortunately, we cannot reference column aliases in other expressions
in the select-list. We can reference column aliases only in GROUP BY,
ORDER BY, or HAVING clauses. See
http://dev.mysql.com/doc/refman/5.0/...ith-alias.html

Regards,
Bill K.

#3 (**permalink**) 02-28-2008, 07:37 AM

"Bill Karwin" <bill@karwin.com> wrote in message
news:e26rjq025q8@enews2.newsguy.com...
> Bosconian wrote:
> > SELECT mr.teamid,
> > SUM( IF( mr.winner = '1', 1, 0 ) ) AS wins,
> > SUM( IF( mr.winner = '0', 1, 0 ) ) AS losses,
> > SUM(wins/losses) AS percentage <----- how to correctly calculate
> > FROM match_result mr
> > GROUP BY mr.teamid
>
> Would this do what you want?
>
> SELECT mr.teamid,
> SUM( IF( mr.winner = '1', 1, 0 ) ) / COUNT(mr.winner) AS wins_pct,
> SUM( IF( mr.winner = '0', 1, 0 ) ) / COUNT(mr.winner) AS losses_pct,
> FROM match_result mr
> GROUP BY mr.teamid
>
> Unfortunately, we cannot reference column aliases in other expressions
> in the select-list. We can reference column aliases only in GROUP BY,
> ORDER BY, or HAVING clauses. See
> http://dev.mysql.com/doc/refman/5.0/...ith-alias.html
>
> Regards,
> Bill K.

I opted to perform this calculation at the page level after all because of
additional considerations. I file this away for future use though--thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

calculate using in-query values