SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding the sum of columns

    I have this query:

    Code mysql:
    SELECT
    	team,
    	img,
    	yyb_wins * 1 + yyb_draws * 0.5 as yyb_weighted_score,
    	ss_wins * 0.25 + ss_wins*(0.25/2) as ss_weighted_score,
    	msn_wins * 0.15 + msn_draws*(0.15/2) as msn_weighted_score
    FROM ac_table
    As you can see, I'm calculating some statistics. However, I need another column created in the results set named "total_weighted_score" which is created from the SUM of yyb_weighted_score, ss_weighted_score, and msn_weighted_score. I've tried this:

    Code mysql:
    SELECT
    	team,
    	img,
    	yyb_wins * 1 + yyb_draws * 0.5 as yyb_weighted_score,
    	ss_wins * 0.25 + ss_wins*(0.25/2) as ss_weighted_score,
    	msn_wins * 0.15 + msn_draws*(0.15/2) as msn_weighted_score,
    	yyb_weighted_score + ss_weighted_score + msn_weighted_score as total_weighted_score
    FROM ac_table
    but it doesn't work. How can I accomplish what I'm trying to do? Thanks! I'm sure it's just something simple that I've missed and don't know how to search Google properly for (I don't really know what to search for, errgh).
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are two options:
    Code:
    SELECT team
         , img
         , yyb_wins * 1 + yyb_draws * 0.5       AS yyb_weighted_score
         , ss_wins * 0.25 + ss_wins*(0.25/2)    AS ss_weighted_score
         , msn_wins * 0.15 + msn_draws*(0.15/2) AS msn_weighted_score
         , yyb_wins * 1 + yyb_draws * 0.5      +
           ss_wins * 0.25 + ss_wins*(0.25/2)   +
           msn_wins * 0.15 + msn_draws*(0.15/2) AS total_weighted_score
      FROM ac_table
    Code:
    SELECT team
         , img
         , yyb_weighted_score
         , ss_weighted_score
         , msn_weighted_score
         , yyb_weighted_score +
           ss_weighted_score  +
           msn_weighted_score   AS total_weighted_score
      FROM (
           SELECT team
                , img
                , yyb_wins * 1 + yyb_draws * 0.5       AS yyb_weighted_score
                , ss_wins * 0.25 + ss_wins*(0.25/2)    AS ss_weighted_score
                , msn_wins * 0.15 + msn_draws*(0.15/2) AS msn_weighted_score
             FROM ac_table
           ) AS dt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, it works great!! I figured it could be something like that... b) You rock!
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs


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
  •