SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Averaging the top X scores

    I'm resurrecting an app I wrote two years ago that we used to collect (and judge) online award applications. People could submit nominations, and then judges could score them.

    The version from two years ago was really straightforward. There were 8 criteria with a score of 1-5, so everything was nice and round, and I could arrive at 100% very easily.

    This year there's a twist. There are 9 criteria, and they only want to count the 7 highest scores out of those. I have to admit, I have NO idea how to implement this.

    Here's my SQL from the old version, modified for the 9 criteria:

    Code SQL:
    SELECT nominees.nom_id
    		, nominees.nom_title
    		, nominees.nom_summary
    		, nominees.nom_firstname & ' ' &  nom_lastname AS nominee
    		, judges.user_id AS judge_id
    		, judges.user_name AS judge
    		, users.user_name AS nominator
    		, users.user_org
    		, a.avg_score
    		, ROUND(scores.cpi_score * 2.222
    			+ scores.inno_score * 2.222
    			+ scores.pc_score * 2.222
    			+ scores.int_score * 2.222
    			+ scores.equ_score * 2.222
    			+ scores.ls_score * 2.222
    			+ scores.drer_score * 2.222
    			+ scores.iis_score * 2.222
    			+ scores.css_score * 2.222) AS total_score
    FROM ( ( ( ( (
           nominees
    INNER JOIN users
    		ON users.user_id = nominees.submit_id
    		)
    LEFT OUTER JOIN judgenom
    		ON judgenom.nomination_id = nominees.nom_id
    		)
    LEFT OUTER JOIN users AS judges
    		ON judges.user_id = judgenom.judge_id
    		)
    LEFT OUTER JOIN scores
    		ON scores.nomination_id = nominees.nom_id
    		)
    LEFT OUTER JOIN ( 
    				SELECT scores.nomination_id
    						, AVG( scores.cpi_score * 2.222
    								+ scores.inno_score * 2.222
    								+ scores.pc_score * 2.222
    								+ scores.int_score * 2.222
    								+ scores.equ_score * 2.222
    								+ scores.ls_score * 2.222
    								+ scores.drer_score * 2.222
    								+ scores.iis_score * 2.222
    								+ scores.css_score * 2.222) AS avg_score
    				FROM scores
    				GROUP BY scores.nomination_id ) AS a
    				ON a.nomination_id = nominees.nom_id
    				)
    WHERE scores.judge_id = judgenom.judge_id
    OR scores.judge_id IS NULL
    ORDER BY avg_score DESC, nominees.nom_id, judgenom.judge_id

    Any hints on how to make it consider only the top seven submitted scores?
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SitePoint Member phobucket's Avatar
    Join Date
    Jan 2011
    Location
    Chilladelphia
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The implementation varies depending on what DB platform you are using, but generally

    Step 1) normalize the scores with a score name and values in a subquery or temp table.
    (SELECT nomination_id, "CPI" as score_type, cpi_score as score
    FROM scores
    UNION
    SELECT nomination_id, "INNO" as score_type, inno_score as score
    FROM scores
    UNION
    ...
    SELECT nomination_id, "CSS" as score_type, css_score as score
    FROM scores) as scores1

    Step 2) Rank the results
    Many DBs have a RANK function. IIRC, MySQL does not, but these links may be of some help. (I like the second link with the UDF)
    Page 2 - MySQL Rank Function ? - Dev Shed
    How to get rank using mysql query | Thinkdiff.net

    Step 3) Filter the results to RANK <=7

    Step 4) Join these results back into the remainder of your query.

    Hopefully this can get you pointed in the right direction. The unions are a bit obnoxious, but it will work.


    Ben

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    What is the server-side programming language being used?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    We're using ColdFusion (8) and MS Access (I know, I know, UGH!)

    I have a feeling Access might not have the RANK function Ben was talking about.
    <cfset myblog = "http://cydewaze.org/">

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I had a good suggestion to take care of the math in the code rather than in the SQL, so I think I'm going to try that route. I'll post back with my progress.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yep got it done.

    I brought the scores into an array, sorted them, then deleted the bottom one (two passes). Works a charm!
    <cfset myblog = "http://cydewaze.org/">


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
  •