CODEWITHSUNDEEP
mysqlsqlsumaverage
ak85
ak85

Reputation: 4264

MySQL sum avg of last x number of results where results can be in different columns

I have a school debating database where I want to be able to see what score each school has averaged over their last 5 debates. If the schoold is hosting the debate there score is recorded in a column called hostscore if they are visiting their score goes in visitscore.

This is an extract of the data I am working with

CREATE TABLE schools (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE debates (
    debateid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    debatedate DATE NOT NULL,
    hostid INT,
    visitid INT,
    hostscore INT,
    visitscore INT
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

If I do 

SELECT debates.debateid, DATE_FORMAT(debates.debatedate,'%m-%d') AS DATE, school1.name AS HOST, school2.name AS VISITOR, debates.hostscore, debates.visitscore
FROM debates 
INNER JOIN schools as school1 ON debates.hostid=school1.id 
INNER JOIN schools as school2 ON debates.visitid=school2.id 
WHERE ((school1.id = 1 OR school2.id =1) ) AND debatedate < CURDATE()
ORDER BY debatedate DESC LIMIT 0 , 5

I can see their last 5 debates.

If I do the below.

SELECT visitid,
(
SELECT 
(
((SELECT sum(visitscore) FROM debates WHERE (visitid=1) AND debatedate < CURDATE()) + (SELECT sum(hostscore) FROM debates WHERE (hostid=1) AND debatedate < CURDATE()))
/
(SELECT COUNT(*) FROM debates WHERE ((visitid=1)or(hostid=1)) AND debatedate < CURDATE())
) 
FROM debates 
INNER JOIN schools as school1 ON debates.hostid=school1.id 
INNER JOIN schools as school2 ON debates.visitid=school2.id 
LIMIT 0,1
)
AS AVGSCORE 
FROM debates 
WHERE visitid=1 
LIMIT 0,1

I can see their avg score per debate for the year.

But I can't work out how to get show the last 5 (overall) debates (the avg where hostid=1 and visitid =1 in the below data)

    ----------------------------------------------------------------------
    |DEBATEID   |DATE   |HOST       |VISITOR    |HOSTSCORE  |VISITSCORE |
    |20         |09-22  |St Luke    |St Thomas  |82         |84         |
    |16         |08-22  |St Thomas  |St Simon   |91         |88         |
    |15         |08-12  |St Luke    |St Thomas  |75         |64         |
    |11         |07-12  |St Thomas  |St Simon   |72         |64         |
    |10         |06-28  |St Luke    |St Thomas  |82         |84         |
    ----------------------------------------------------------------------

From working out manually the result in this example should be 79. I have tried a number of things here. I thought this looked good but it produces results which are no where near what I am looking for. What am I doing wrong? See this SQL Fiddle for test data

SELECT sum(visitid),
(SELECT 
(
(
(SELECT sum(visitscore) FROM (SELECT visitscore FROM debates WHERE (hostid=1) AND debatedate < CURDATE() ORDER BY debatedate DESC LIMIT 0,5) as awayavgpg1)
+ 
(SELECT sum(visitscore) FROM (SELECT visitscore FROM debates WHERE (visitid=1) AND debatedate < CURDATE() ORDER BY debatedate DESC LIMIT 0,5) as awayavgpg2)
)
/
(SELECT COUNT(*) FROM (SELECT * FROM debates WHERE (hostid=1) or (visitid=1) AND debatedate < CURDATE()LIMIT 0,5) as awayavgpg3)
)
FROM debates 
LIMIT 0,1) AS AVSCORE
FROM debates 
WHERE visitid=1 
LIMIT 0,1

Upvotes: 1

Views: 260

Answers (1)

Barmar
Barmar

Reputation: 780994

Use a UNION to merge the columns into one:

SELECT AVG(score) avscore
FROM (SELECT debatedate, score
      FROM (SELECT debatedate, hostscore score
            FROM debates
            WHERE hostid = 1
            UNION
            SELECT debatedate, visitscore score
            FROM debates
            WHERE visitid = 1) x
      ORDER BY debatedate DESC
      LIMIT 5) y

Upvotes: 2

Related Questions