[Solved] Getting data in table from SQL querie

Discuss the database features
Post Reply
sdkautoa
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

[Solved] Getting data in table from SQL querie

Post by sdkautoa »

Can someone help me with this code, I would like to get the last three GolferScore per golfer to be captured in the Test table. Been fighting with it for a while without any luck.
Thanks, sdkautoa

Code: Select all


DROP TABLE "Test" IF EXISTS;
Select
	"ID",
    "LastName",											
    "FirstName",										
    "#",												
    "Last_three_Average"
 INTO "Test"								
FROM
    "GolferContactTBL" 

 
RIGHT JOIN 												
    (
    SELECT
        "GolferID",										
        COUNT( * ) as "#",								
        AVG( "GolferScore") "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
     "LastName"
Last edited by Hagar Delest on Sat Jan 20, 2018 1:18 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.2 on Windows Vista
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Help needed getting data in table from SQL querie

Post by keme »

It would be useful to know a few details:
  • What platform are you on ("embedded" database in Base, connection to a local ODBC source, external MySQL)?
  • What output do you get (wrong data, error message, empty table)?
Haven't tried to debug your query, but a few thougths after a quick read through:
  • The # has a special meaning with some DB systems (temporary object). I have never used it in a field name, so I don't know the implications?
  • I believe the SELECT ... INTO construct is not supported by all RDBMS'. Can you use CREATE TABLE ... AS SELECT ... FROM ... instead?
  • Also, the RIGHT OUTER JOIN is not universally supported. Usually easy to work around by rearranging query blocks.
sdkautoa
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Post by sdkautoa »

I created the data base in Open Office and now using it in Libreoffice. I have no problems with how it works. I'm trying to expand this query to give me the last three scores that it's computing for an average. The code at this time gives me the average for each player, but I'd also like it to populate the table with the last three scores it used to get me the averages. I just can't figure out how to code it in there for it to happen. I think its an HSQLDB Embedded.
OpenOffice 4.1.2 on Windows Vista
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Help needed getting data in table from SQL querie

Post by eremmel »

In Base queries have to return a result (i.e. should be SELECT-queries). When you want to apply a DML or DDL based query in Base you have to go via menu:Tools -> 'SQL...' interface.
I assume that the SQL statement (without the 'INTO...' clause) runs just fine and give you the expected results. But looking closer to your query I've some doubts about it's correctness. Here a corrected version that might run as a pure select.

Code: Select all

    Select "GolferContactTBL"."ID",
        "GolferContactTBL"."LastName",                                 
        "GolferContactTBL"."FirstName",                              
        "Q3"."#",                                    
        "Q3"."Last_three_Average"
    FROM "GolferContactTBL"
    RIGHT JOIN (
        SELECT "Q2"."GolferID",                              
            COUNT( * ) as "#",                        
            AVG( "Q2"."GolferScore") "Last_three_Average"
        FROM (
            SELECT "A"."GolferID",                           
                "A"."GolfDate",                           
                "A"."GolferScore"                        
            FROM "RoundTBL" "A"
            INNER JOIN "RoundTBL" "B"
                ON "A"."GolfDate" <= "B"."GolfDate"
                    AND "A"."GolferID" = "B"."GolferID"
            GROUP BY "GolferID", "GolfDate", "GolferScore"
            HAVING COUNT( * ) <= 3
        ) AS "Q2"
        GROUP BY "Q2"."GolferID"
    ) AS "Q3"
        ON "GolferContactTBL"."ID" = "Q3"."GolferID"
    ORDER BY "LastName"

Changes made:
1. LEFT JOIN --> INNER JOIN due to "<=" in ON-clause
2. Alias Q2 needed on derived table (i.e. "( SELECT ...") in FROM-clause.
3. fields in SELECT need to have alias as well when field name is ambiguous (better to use always alias).
Try to run query in direct SQL mode because better error messages.

P.S.
Structure of query is fine. This type of queries are the harder ones to tackle for most people :super:
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
sdkautoa
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Post by sdkautoa »

Thanks for making the code work better but what I really need is to get it to show the last 3 golf scores it used to get the average for the last 3 golf scores.
Thanks sdkautoa.
OpenOffice 4.1.2 on Windows Vista
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Post by chrisb »

sdkautoa,
you have a problem because for each golfer you have up to 3 individual scores.
by using the AVG function with grouping those 3 rows are reduced to one.
so the issue is how do we merge those three rows into one.
i have used conditional aggregation to create three fields, one for each score.
please execute the code from 'Tools SQL'.
 Edit: 19-Jan-2018 04:45 after testing discovered problem with code concerning the count() routine. replaced old code with new. sorry for that.
dates must be unique. if a golfer plays more than one round on the same date then this code will fail. the simple solution is to replace the date field with a timestamp. 

Code: Select all

drop table "Test" if exists;
select R."GolferID", G."LastName", G."FirstName", avg(R."GolferScore") "AverageScore",
max(case when R."#" = 0 then R."GolferScore" end) "LastScore",
max(case when R."#" = 1 then R."GolferScore" end) "2ndLastScore",
max(case when R."#" = 2 then R."GolferScore" end) "3rdLastScore"
into "Test"                        

from
(
	select R1."GolferID", R1."GolferScore", count(R2."GolfDate") "#"
	from "RoundTBL" R1
	left join "RoundTBL" R2
	on R2."GolferID" = R1."GolferID" and R2."GolfDate" > R1."GolfDate"
	group by R1."GolferID", R1."GolferScore"
	having count(R2."GolfDate") <= 2
) R
join "GolferContactTBL" G on G.ID = R."GolferID"
group by "GolferID", "LastName", "FirstName"
--order by "LastName"
Last edited by chrisb on Fri Jan 19, 2018 11:34 am, edited 1 time in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Help needed getting data in table from SQL querie

Post by eremmel »

Chrisb, that is a nice pattern.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
sdkautoa
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Post by sdkautoa »

Don't seem to be getting the results i'm looking for. When I'm asking for the average of the last 3 rounds played, it's the 3 newest rounds played. The program I posted worked well on getting me the newest 3 rounds the golfer played average, I just needed it to show me those newest 3 scores it used for the averages. Things to take into account, not every golfer golfed every date, and there is never a chance that a golfer will golf twice on a date.
Thanks sdkautoa.
OpenOffice 4.1.2 on Windows Vista
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Post by chrisb »

sdkautoa,
look at the image.
the query clearly shows the 3 most recent scores & the average of those 3 scores for each individual golfer.
remember the average is an integer which will have been rounded down.
the average score shown by your query is identical to the average score shown by my query.
my query also shows the 3 most recent scores from which the average is derived.

can you show us using data from the table image the exact result you are expecting?
Golf_Image_19-Jan-2018.png
i have uploaded my test database which will enable any interested forum users to analyse my findings.
Golf_19-01-2018.odb
(5.02 KiB) Downloaded 170 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
sdkautoa
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Post by sdkautoa »

Screenshot_2018-01-19_13-41-05.png
OpenOffice 4.1.2 on Windows Vista
sdkautoa
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Post by sdkautoa »

The output somehow misses my own rounds completely, I didn't even make the list.
OpenOffice 4.1.2 on Windows Vista
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Post by chrisb »

sdkautoa, if you are using the code currently contained in my post dated Fri Jan 19, 2018 1:50 am then you need to upload a cut down copy of your database.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Post by chrisb »

sdkautoa,
i do apologise.
line 14 in the query which reads:

Code: Select all

group by R1."GolferID", R1."GolferScore"
should read:

Code: Select all

group by R1."GolferID", R1."GolfDate", R1."GolferScore"
the result of not grouping by R1."GolfDate" meant that if an individual golfer recorded duplicate scores then only one those rows would be selected.
hopefully this cures the error.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
sdkautoa
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Post by sdkautoa »

Thank you so much chrisb, it worked like a dream! I appreciate your help so much.
Thanks again sdkautoa.
OpenOffice 4.1.2 on Windows Vista
Post Reply