MySQL Forums
Forum List  »  Newbie

Re: SUM problem.
Posted by: Patric Andersson
Date: March 21, 2009 11:13AM

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 ?

Options: ReplyQuote


Subject
Written By
Posted
March 12, 2009 03:29AM
March 12, 2009 09:52AM
March 13, 2009 07:10PM
March 13, 2009 11:10PM
March 14, 2009 04:01PM
March 14, 2009 06:40PM
March 15, 2009 07:54AM
March 15, 2009 10:41AM
March 15, 2009 10:35PM
March 16, 2009 09:33AM
March 16, 2009 09:52AM
March 16, 2009 02:46PM
March 16, 2009 04:15PM
March 17, 2009 02:11PM
March 17, 2009 05:03PM
March 17, 2009 06:31PM
March 17, 2009 10:17PM
March 18, 2009 06:03AM
March 15, 2009 05:41PM
March 15, 2009 07:14PM
March 16, 2009 08:34AM
March 18, 2009 12:52AM
March 21, 2009 05:58AM
Re: SUM problem.
March 21, 2009 11:13AM
March 22, 2009 05:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.