Professional Web Applications Themes

calculate using in-query values - MySQL

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

  1. #1

    Default calculate using in-query values

    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



    Bosconian Guest

  2. #2

    Default Re: calculate using in-query values

    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
    [url]http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: calculate using in-query values

    "Bill Karwin" <billkarwin.com> wrote in message
    news:e26rjq025q8enews2.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
    > [url]http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html[/url]
    >
    > 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!


    Bosconian Guest

Similar Threads

  1. Datagrid don't show all values from query
    By jorge_sms in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: September 19th, 03:31 PM
  2. Calculate values
    By Cindy587 in forum Macromedia ColdFusion
    Replies: 7
    Last Post: March 8th, 01:00 AM
  3. add values without using SUM in query?
    By sean in forum ASP Database
    Replies: 1
    Last Post: May 10th, 12:42 PM
  4. [PHP] Sum a column of values from a MySQL query
    By Ben C. in forum PHP Development
    Replies: 8
    Last Post: August 5th, 01:50 PM
  5. checking for values in a query
    By Algo in forum Microsoft Access
    Replies: 3
    Last Post: July 17th, 07:50 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139