[Solved] Getting data in table from SQL querie

Discuss the database features

[Solved] Getting data in table from SQL querie

Postby sdkautoa » Thu Jan 18, 2018 9:37 am

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   Expand viewCollapse view

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
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Postby keme » Thu Jan 18, 2018 11:40 am

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.
User avatar
keme
Volunteer
 
Posts: 2987
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Help needed getting data in table from SQL querie

Postby sdkautoa » Thu Jan 18, 2018 12:05 pm

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
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Postby eremmel » Thu Jan 18, 2018 1:34 pm

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   Expand viewCollapse view
    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:
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1016
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Help needed getting data in table from SQL querie

Postby sdkautoa » Thu Jan 18, 2018 10:53 pm

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
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Postby chrisb » Fri Jan 19, 2018 1:50 am

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   Expand viewCollapse view
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.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 175
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Postby eremmel » Fri Jan 19, 2018 9:50 am

Chrisb, that is a nice pattern.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1016
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Help needed getting data in table from SQL querie

Postby sdkautoa » Fri Jan 19, 2018 5:09 pm

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
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Help needed getting data in table from SQL querie

Postby chrisb » Fri Jan 19, 2018 7:54 pm

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 23 times
open office 4.1.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 175
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Postby sdkautoa » Fri Jan 19, 2018 8:44 pm

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

Postby sdkautoa » Fri Jan 19, 2018 8:51 pm

The output somehow misses my own rounds completely, I didn't even make the list.
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

Postby chrisb » Fri Jan 19, 2018 9:25 pm

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.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 175
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Postby chrisb » Fri Jan 19, 2018 11:38 pm

sdkautoa,
i do apologise.
line 14 in the query which reads:
Code: Select all   Expand viewCollapse view
group by R1."GolferID", R1."GolferScore"

should read:
Code: Select all   Expand viewCollapse view
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.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 175
Joined: Mon Jun 07, 2010 4:16 pm

Re: Help needed getting data in table from SQL querie

Postby sdkautoa » Sat Jan 20, 2018 3:37 am

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
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests