Re: SUM problem.

[Patric Andersson]

Ok, 3'rd time the charm then :)
Hope this is the info you need to be able to help me.
This is a simplified tables of what I currently use, but all the keys that is needed (I think).

CREATE TABLE lepok_anvandare(id int(10), fornamn varchar(255), efternamn varchar(255));

CREATE TABLE lepok_resultat(id int(10), turnering int(10), deltagare varchar(255), poang int(10));

CREATE TABLE lepok_turneringar(id int(10), datum date, typ varchar(255));

INSERT INTO lepok_anvandare VALUES ('1','Patric','Andersson'),('2','Anders','Andersson'),('3','Fredrik','Johansson');

INSERT INTO lepok_resultat VALUES ('1','1','1','10'),('2','2','1','5'),('3','3','1','12'),('4','1','2','18'),
('5','2','2','7'),('6','3','2','1'),('7','1','3','10'),('8','2','3','10'),('9','3','3','10');

INSERT INTO lepok_turneringar VALUES ('1','2008-02-10','pligan'),('2','2008-02-11','pligan'),('3','2008-02-12','pligan');

Ok, and here is the current SELECT I'm useing.

SELECT lepok_anvandare.id AS anvid, fornamn, efternamn,
typ, lepok_turneringar.id, datum, YEAR(datum), YEAR(CURRENT_DATE()),
SUM(poang), MAX(poang), deltagare, turnering
FROM lepok_anvandare, lepok_resultat, lepok_turneringar
WHERE lepok_anvandare.id=deltagare AND typ='pligan' AND lepok_turneringar.id=turnering
AND YEAR(datum)=YEAR(CURRENT_DATE())
GROUP BY lepok_anvandare.id ORDER BY SUM(poang) DESC, MAX(poang) DESC

In this temp database, it would sort the 3 users based on the 3 torneys.
But what if I wanted to sort the 3 users based on their 2 best results ?
If it's possible to write a SELECT for that, I think it should be easy for me to convert the 2 out of 3 to 8 out of 11 :)

And again, I hope this was the information you needed.