CODEWITHSUNDEEP
mysql
Bonttimo
Bonttimo

Reputation: 671

SQL join tables and get Average

I asked yesterday a little bit similar question (I thought that that was my problem but later i realised that there was a fault). But that question got couple of nice answers and it did not make sense to change that question. And i think this question is enough different.

Question:

I have four tables and i need to calculate the Average points that each School has gotten.

Problem: the School Average should be calculated by the two latest Points each Team has gotten. At the moment the Query calculates all the points a Teams has gotten in the average.

A School can have multiple Teams and Teams can have multiple points. And from each team only the two latest points should be calculated in the School Average. Each School should also get the proper City KAID (CITY_ID). In the sqlFiddle everything works but the Average is wrong because it calculates all the points a Team has gotten.

I have created a simplificated working: sqlFiddle

The average for SCHOOL1 should be 2,66...

Example:

Let's say that Team10 has 6 points:

TEAM10 3..4..7..0..3..5 = 8 (3+5=8)

Only the latest two points should be calculated in the average (3 and 5). This should happen for all the teams.

I have tried couple of Queries but they don't work.

Query 1 (Problem: calculates all the points):

SELECT SNAME As School, AVG(PTS) As Points, ka.KAID As City_id FROM
     Schools op
     LEFT JOIN Points pi
     ON op.OPID = pi.OPID
     LEFT JOIN Citys ka
     ON op.KAID = ka.KAID
     GROUP BY SNAME, ka.KAID
     ORDER BY City_id, Points, School ASC

Query 2 (Problem: Average wrong and duplicates):

SELECT IFNULL(AVG(PTS), 0) AS AVG, po2.KAID AS KID, SNAME AS SNAM FROM 
    (
    SELECT te1.ID, te1.KAID, po1.PTS, te1.OPID FROM Points po1
    INNER JOIN Teams te1 ON te1.ID = po1.TEID
    GROUP BY po1.TEID, te1.ID HAVING count(*) >= 2
    )
po2 INNER JOIN Schools sch1 ON po2.KAID = sch1.KAID
    GROUP BY sch1.SNAME, sch1.OPID
    ORDER BY po2.ID DESC

I am quite new to sql I have tried different Queries but i haven't gotten this to work properly.

If something is not clear please ask i will try to Explain it better.

Upvotes: 1

Views: 3095

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

try running this...

SELECT 
    SNAME As School, 
    SUM(pts)/ count(*) As Points, 
    ka.KAID As City_id 
FROM Schools op
LEFT JOIN Points pi
   ON op.OPID = pi.OPID
LEFT JOIN Citys ka
   ON op.KAID = ka.KAID
GROUP BY SNAME, ka.KAID
ORDER BY City_id, Points, School ASC

DEMO

From what I see you have for the first school and the first city 8 rows with the sum = 29.
29/8 = 3.25.. you are joining the tables on the correct fields and the query is returning the rows in the table based on the opid and kaid so it seems the results are correct.. i'm guessing the avg function is not including the 0's or something but the results are there

EDIT:

to get it for the two newest rows you need to look at the greatest id per school and then the second greatest.. this will do what you want.

SELECT 
    SNAME As School, 
    SUM(pts)/ count(*) As Points, 
    ka.KAID As City_id 
FROM Schools op
LEFT JOIN Points pi ON op.OPID = pi.OPID
LEFT JOIN Citys ka ON op.KAID = ka.KAID
JOIN
(   (   SELECT MAX(id) as f_id 
        FROM points
        GROUP BY TEID
        ORDER BY f_id
    )
    UNION
    (   SELECT p1.id 
        FROM
        (   SELECT MAX(id) as t_id 
            FROM points
            GROUP BY TEID
            ORDER BY t_id
        )t
        LEFT JOIN points p1 on p1.id = (t.t_id -1)
    )
) temp ON temp.f_id = pi.id
GROUP BY SNAME, ka.KAID
ORDER BY City_id, Points, School ASC;

ANOTHER DEMO

Upvotes: 1

Related Questions