SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ordering a query then doing a running total

    Hi There,

    I would appreciate some help on a query that is beating me at the moment. I have data in a table I would like to first put in order and then calculate a running total. In the example below the query works and shows the 'Cumulative' column as ordered, however, I want the 'ScoreC/ScoreD' column to be in ascending order, which it currently isn't. Once 'ScoreC/ScoreD' is ordered correctly I then want it to calculate the Cumulative column. Any help would be appreciated:

    query = "SELECT colname, scoreA, scoreB, scoreC, ScoreC/ScoreD, ScoreD, (SELECT Sum(ScoreD) FROM mytable x WHERE x.ScoreC/ScoreD <= mytable.ScoreC/ScoreD) AS Cumulative FROM mytable ORDER BY Cumulative"

    Colname: ScoreC:ScoreC/ScoreD:Cumulative
    J : -1995 : -0.042 : 47062
    P : 10700 : 0.055 : 244531
    S : 10711 : 0 : 2212263
    K : 16995 : 0.194 : 2221014
    W : 27418 : 1.282 : 2223154
    B : 187862 : 0.702 : 2249932

    Thanks

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so why aren't those columns in your ORDER BY clause then?

  3. #3
    SitePoint Member
    Join Date
    Jul 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I change the query to be ...ORDER BY ScoreC/ScoreD then I get the results in the correct order I want but the Cumulative column is then out of sync:

    Colname: ScoreC:ScoreC/ScoreD:Cumulative
    J : -1995 : -0.042 : 47062
    S : 10711 : 0 : 2212263
    P : 10700 : 0.055 : 244531
    K : 16995 : 0.194 : 2221014
    B : 187862 : 0.702 : 2249932
    W : 27418 : 1.282 : 2223154

    So somehow I need for it to ORDER BY ScoreC/ScoreD first and then calculate the Cumulative column after this?

    query = "SELECT colname, scoreA, scoreB, scoreC, ScoreC/ScoreD, ScoreD, (SELECT Sum(ScoreD) FROM mytable x WHERE x.ScoreC/ScoreD <= mytable.ScoreC/ScoreD) AS Cumulative FROM mytable ORDER BY Cumulative"


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
  •