[Solved] Avg the last 3 golf scores

Creating tables and queries

[Solved] Avg the last 3 golf scores

Postby sdkautoa » Sat Jan 23, 2016 12:15 pm

It's been at least 10 years since I done any type of coding so I'm kind of green around the gills.
So any help would be appreciated.
I'm trying to grab a max of the last 3 golf scores (some have only 1 or 2 scores inputted) to average per player.
GolferContactTBL With fields of (ID, LastName, FirstName), RoundTBL with fields of (GolferID, GolfDate,GolferScore,RecordNum). I have have the two tables linked with "ID" to "GolferID" as 1 to many relationship.
I'v been fighting with trying to make a query in both design mode and Sql with no luck. didn't think it should have been too hard to do but I'm hitting a brick wall.
Thanks Sdkautoa
Last edited by sdkautoa on Sun Jan 24, 2016 7:24 am, edited 1 time in total.
OpenOffice 4.1.2 on Windows Vista
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Avg the last 3 golf scores

Postby F3K Total » Sat Jan 23, 2016 5:50 pm

Hello,
this should work:
Code: Select all   Expand viewCollapse view
SELECT
    "LastName",
    "FirstName",
    "#",
    "Last_three_Average"
FROM
    "GolferContactTBL"
LEFT JOIN
    (
    SELECT
        "GolferID",
        COUNT( * ) as "#",
        AVG( "GolferScore" * 1.000 ) "Last_three_Average"
    FROM
        (
        SELECT
            "GolferID",
            "GolfDate",
            "GolferScore"
        FROM
            "RoundTBL" "A"
        LEFT JOIN
            "RoundTBL" "B"
        ON
            "A"."GolfDate" <= "B"."GolfDate"
        AND
            "A"."GolferID" = "B"."GolferID"
        GROUP BY
            "GolferID",
            "GolfDate",
            "GolferScore"
        HAVING COUNT( * ) <= 3
        )
    GROUP BY
        "GolferID"
    ) AS Q3
ON
    "GolferContactTBL"."ID" = "Q3"."GolferID"
ORDER BY
     "Last_three_Average"

R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Avg the last 3 golf scores

Postby sdkautoa » Sat Jan 23, 2016 6:09 pm

Wow... That was amazingly quick and I'v been hitting this for at least a week every day and getting nowhere fast. Thanks again F3K Total! I really appreciate your help. Yahooooo Now I can move on and hopefully build on this:-)
OpenOffice 4.1.2 on Windows Vista
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Avg the last 3 golf scores

Postby F3K Total » Sat Jan 23, 2016 6:16 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest