Re: SUM problem.
Ok, I actually solved my problem :)
Might not have been the most optimal solution but it works :)
I do this by creating 2 temporary tables that I drop at the end of the session.
First thing I do is get the information I need from my 3 tables to create my first temporary table :
SELECT
deltagare,
poang,
fornamn,
efternamn,
datum
FROM
lepok_resultat
JOIN
lepok_anvandare
ON
deltagare=lepok_anvandare.id
JOIN
lepok_turneringar
ON
turnering=lepok_turneringar.id AND
typ='poangligan' AND
YEAR(datum)=YEAR(CURRENT_DATE())-1
ORDER BY
deltagare,
poang DESC;
Then I create the first temporary table :
CREATE TABLE IF NOT EXISTS
Lepok_TempTable (
id int not null auto_increment,
primary key(id),
temp_id int(10),
temp_poang int(10),
temp_fornamn varchar(255),
temp_efternamn varchar(255),
temp_datum date);
And then (with the help of php code) put the information from the first query into the first temp table.
First I create variables :
$temp_id = $row['deltagare'];
$temp_fornamn = $row['fornamn'];
$temp_efternamn = $row['efternamn'];
$temp_poang = $row['poang'];
$temp_datum = $row['datum'];
And then insert the information :
INSERT INTO Lepok_TempTable (
temp_id,
temp_fornamn,
temp_efternamn,
temp_poang,
temp_datum)
VALUES(
$temp_id,
'$temp_fornamn',
'$temp_efternamn',
'$temp_poang',
'$temp_datum');
Next I need to get just the top8 results for each user from the first temp table.
SELECT
t1.id,
t1.temp_id,
t1.temp_fornamn,
t1.temp_efternamn,
t1.temp_poang,
count(*) AS earlier
FROM
Lepok_TempTable AS t1
JOIN
Lepok_TempTable AS t2
ON
t1.temp_id=t2.temp_id AND
t1.id >= t2.id
GROUP BY
t1.temp_id,
t1.id
HAVING earlier <= 8;
Then I create my second temporary table.
CREATE TABLE IF NOT EXISTS
Lepok_TempTable2 (
id int not null auto_increment,
primary key(id),
temp2_poang int(10),
temp2_anvid int(10),
temp2_fornamn varchar(255),
temp2_efternamn varchar(255))
And set the new variables that need to be inserted into the second temporary table.
$temp2_anvid = $row['temp_id'];
$temp2_poang = $row['temp_poang'];
$temp2_fornamn = $row['temp_fornamn'];
$temp2_efternamn = $row['temp_efternamn'];
And then I insert the information from the query of the first temp table into the second temp table.
INSERT INTO Lepok_TempTable2 (
temp2_anvid,
temp2_fornamn,
temp2_efternamn,
temp2_poang)
VALUES(
'$temp2_anvid',
'$temp2_fornamn',
'$temp2_efternamn',
'$temp2_poang');
And now finaly I can make a query to get the sum of the second temp table to get the final result of the top 8 results of the 11 torneys.
SELECT
temp2_anvid,
temp2_fornamn,
temp2_efternamn,
SUM(temp2_poang) as summa
FROM
Lepok_TempTable2
GROUP BY
temp2_anvid
ORDER BY
summa DESC;
This worked for what I wanted to acomplish but it (most likely) isn't the best solution.
Atm I use 3 querys and 2 temporary tables to get the result I need as it causes problem mixing Aggregate Functions and JOINs, but is it possible to actually solve this without creating temporary tables and 3 querys, but instead just use 1 query ?