Re: SUM problem.
Hi Chris.
First of all, sorry for this LONG post.
Just notised this post today as I haven't read the forums in a few days.
Thanks for your answer and even tho your answer is a tad too complex for me
I think I came up with the exact same conclusion as you yesterday on my own.
What I did (and no, I still haven't got it completly right but I'm well on my way, I hope :P).
What I did yesterday was to actually download mysql localy to my computer (yes, I was actually working with the database remotely useing php codes to call mysql commands and when I added Aggregate Functions to JOINs my results where well, to say the least odd, and I didn't understand why.
However, now when I've been working localy I get more inside depths into my databases. Well, enough rambling, this is what I did.
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
This way I get all the information I need (I actually don't NEED the datum(date) to be exported, but I keep it in there just to make sure I get the correct "answer")
The information I get from this I put into a new table, I first made it a temporary table but then I notised you can't seem to be able to use JOINs in temporary tables so I just made it a normal table that I drop anyway at the end of the session so it doesn't really matter that it's a "normal" table.
This is the information I now get in the new table :
CREATE TABLE `Lepok_TempTable` (
`id` int(11) NOT NULL auto_increment,
`temp_id` int(10) default NULL,
`temp_poang` int(10) default NULL,
`temp_fornamn` varchar(255) default NULL,
`temp_efternamn` varchar(255) default NULL,
`temp_datum` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=323 ;
INSERT INTO `Lepok_TempTable` VALUES (1, 24, 19, 'Patric', 'Andersson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (2, 24, 5, 'Patric', 'Andersson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (3, 24, 3, 'Patric', 'Andersson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (4, 27, 42, 'Anders', 'Andersson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (5, 27, 33, 'Anders', 'Andersson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (6, 27, 20, 'Anders', 'Andersson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (7, 27, 20, 'Anders', 'Andersson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (8, 27, 15, 'Anders', 'Andersson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (9, 27, 14, 'Anders', 'Andersson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (10, 27, 13, 'Anders', 'Andersson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (11, 27, 7, 'Anders', 'Andersson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (12, 27, 2, 'Anders', 'Andersson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (13, 27, 1, 'Anders', 'Andersson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (14, 27, 1, 'Anders', 'Andersson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (15, 28, 15, 'Frank', 'Eriksson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (16, 28, 9, 'Frank', 'Eriksson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (17, 29, 31, 'Magnus', 'Fagrell', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (18, 29, 22, 'Magnus', 'Fagrell', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (19, 29, 20, 'Magnus', 'Fagrell', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (20, 29, 19, 'Magnus', 'Fagrell', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (21, 29, 16, 'Magnus', 'Fagrell', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (22, 29, 9, 'Magnus', 'Fagrell', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (23, 29, 8, 'Magnus', 'Fagrell', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (24, 29, 4, 'Magnus', 'Fagrell', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (25, 29, 4, 'Magnus', 'Fagrell', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (26, 29, 3, 'Magnus', 'Fagrell', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (27, 29, 3, 'Magnus', 'Fagrell', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (28, 30, 39, 'Johan', 'Gjermandsen', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (29, 30, 26, 'Johan', 'Gjermandsen', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (30, 30, 8, 'Johan', 'Gjermandsen', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (31, 30, 1, 'Johan', 'Gjermandsen', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (32, 31, 23, 'Anders', 'Hedlund', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (33, 31, 20, 'Anders', 'Hedlund', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (34, 32, 13, 'Anton', 'Appelgren', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (35, 32, 4, 'Anton', 'Appelgren', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (36, 33, 22, 'Greger', 'Appelgren', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (37, 35, 7, 'Tobias', 'Blomqvist', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (38, 36, 33, 'Jonas', 'Edmark', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (39, 36, 30, 'Jonas', 'Edmark', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (40, 36, 28, 'Jonas', 'Edmark', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (41, 36, 25, 'Jonas', 'Edmark', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (42, 36, 22, 'Jonas', 'Edmark', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (43, 36, 20, 'Jonas', 'Edmark', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (44, 36, 17, 'Jonas', 'Edmark', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (45, 36, 15, 'Jonas', 'Edmark', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (46, 36, 12, 'Jonas', 'Edmark', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (47, 36, 9, 'Jonas', 'Edmark', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (48, 36, 8, 'Jonas', 'Edmark', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (49, 37, 51, 'Urban', 'Eriksson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (50, 37, 38, 'Urban', 'Eriksson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (51, 37, 22, 'Urban', 'Eriksson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (52, 37, 19, 'Urban', 'Eriksson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (53, 37, 19, 'Urban', 'Eriksson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (54, 37, 11, 'Urban', 'Eriksson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (55, 37, 2, 'Urban', 'Eriksson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (56, 37, 1, 'Urban', 'Eriksson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (57, 37, 1, 'Urban', 'Eriksson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (58, 38, 23, 'Jörgen', 'Fredriksson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (59, 38, 17, 'Jörgen', 'Fredriksson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (60, 38, 11, 'Jörgen', 'Fredriksson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (61, 38, 9, 'Jörgen', 'Fredriksson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (62, 38, 7, 'Jörgen', 'Fredriksson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (63, 38, 6, 'Jörgen', 'Fredriksson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (64, 39, 19, 'Stefan', 'Hagelin', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (65, 39, 10, 'Stefan', 'Hagelin', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (66, 40, 94, 'Ari', 'Hissa', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (67, 40, 36, 'Ari', 'Hissa', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (68, 40, 36, 'Ari', 'Hissa', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (69, 40, 22, 'Ari', 'Hissa', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (70, 40, 19, 'Ari', 'Hissa', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (71, 40, 18, 'Ari', 'Hissa', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (72, 40, 12, 'Ari', 'Hissa', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (73, 40, 8, 'Ari', 'Hissa', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (74, 40, 6, 'Ari', 'Hissa', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (75, 41, 82, 'Mikael', 'Jansson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (76, 41, 11, 'Mikael', 'Jansson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (77, 41, 3, 'Mikael', 'Jansson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (78, 41, 3, 'Mikael', 'Jansson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (79, 41, 2, 'Mikael', 'Jansson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (80, 41, 1, 'Mikael', 'Jansson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (81, 42, 17, 'Patrik', 'Johansson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (82, 42, 11, 'Patrik', 'Johansson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (83, 43, 32, 'Christian', 'Lambertsson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (84, 43, 24, 'Christian', 'Lambertsson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (85, 43, 16, 'Christian', 'Lambertsson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (86, 43, 6, 'Christian', 'Lambertsson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (87, 44, 39, 'Johan', 'Larsson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (88, 44, 34, 'Johan', 'Larsson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (89, 44, 33, 'Johan', 'Larsson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (90, 44, 30, 'Johan', 'Larsson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (91, 44, 26, 'Johan', 'Larsson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (92, 44, 21, 'Johan', 'Larsson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (93, 44, 10, 'Johan', 'Larsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (94, 44, 4, 'Johan', 'Larsson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (95, 47, 48, 'Collin', 'Ogolla', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (96, 47, 5, 'Collin', 'Ogolla', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (97, 47, 2, 'Collin', 'Ogolla', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (98, 48, 40, 'Stefan', 'Qvist', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (99, 48, 5, 'Stefan', 'Qvist', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (100, 49, 35, 'Rune', 'Vold', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (101, 49, 28, 'Rune', 'Vold', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (102, 49, 26, 'Rune', 'Vold', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (103, 49, 17, 'Rune', 'Vold', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (104, 49, 7, 'Rune', 'Vold', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (105, 49, 7, 'Rune', 'Vold', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (106, 49, 5, 'Rune', 'Vold', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (107, 50, 14, 'Martin', 'Carmesten', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (108, 50, 6, 'Martin', 'Carmesten', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (109, 51, 39, 'Jonas', 'Jansson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (110, 51, 23, 'Jonas', 'Jansson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (111, 51, 15, 'Jonas', 'Jansson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (112, 51, 14, 'Jonas', 'Jansson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (113, 51, 12, 'Jonas', 'Jansson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (114, 51, 5, 'Jonas', 'Jansson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (115, 52, 28, 'Stefan', 'Bergström', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (116, 53, 31, 'Peter', 'Rosin', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (117, 53, 28, 'Peter', 'Rosin', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (118, 53, 10, 'Peter', 'Rosin', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (119, 53, 10, 'Peter', 'Rosin', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (120, 53, 9, 'Peter', 'Rosin', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (121, 53, 6, 'Peter', 'Rosin', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (122, 54, 18, 'Henrik', 'Eriksson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (123, 54, 9, 'Henrik', 'Eriksson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (124, 54, 4, 'Henrik', 'Eriksson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (125, 54, 3, 'Henrik', 'Eriksson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (126, 55, 3, 'Tony', 'Olsson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (127, 56, 34, 'Patrik', 'Zetterqvist', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (128, 56, 23, 'Patrik', 'Zetterqvist', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (129, 56, 17, 'Patrik', 'Zetterqvist', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (130, 56, 12, 'Patrik', 'Zetterqvist', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (131, 56, 12, 'Patrik', 'Zetterqvist', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (132, 56, 11, 'Patrik', 'Zetterqvist', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (133, 56, 9, 'Patrik', 'Zetterqvist', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (134, 56, 8, 'Patrik', 'Zetterqvist', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (135, 58, 46, 'Fredrik', 'Ståhl', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (136, 58, 33, 'Fredrik', 'Ståhl', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (137, 58, 27, 'Fredrik', 'Ståhl', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (138, 58, 24, 'Fredrik', 'Ståhl', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (139, 58, 24, 'Fredrik', 'Ståhl', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (140, 58, 16, 'Fredrik', 'Ståhl', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (141, 58, 15, 'Fredrik', 'Ståhl', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (142, 58, 12, 'Fredrik', 'Ståhl', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (143, 58, 2, 'Fredrik', 'Ståhl', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (144, 58, 2, 'Fredrik', 'Ståhl', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (145, 58, 2, 'Fredrik', 'Ståhl', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (146, 59, 36, 'Henrik', 'Sjöö', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (147, 59, 33, 'Henrik', 'Sjöö', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (148, 59, 9, 'Henrik', 'Sjöö', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (149, 61, 56, 'Magnus', 'Nilsson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (150, 61, 31, 'Magnus', 'Nilsson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (151, 61, 29, 'Magnus', 'Nilsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (152, 61, 22, 'Magnus', 'Nilsson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (153, 61, 19, 'Magnus', 'Nilsson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (154, 61, 19, 'Magnus', 'Nilsson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (155, 61, 14, 'Magnus', 'Nilsson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (156, 61, 13, 'Magnus', 'Nilsson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (157, 61, 13, 'Magnus', 'Nilsson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (158, 61, 9, 'Magnus', 'Nilsson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (159, 61, 1, 'Magnus', 'Nilsson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (160, 63, 1, 'Peter', 'Nilsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (161, 64, 60, 'Conny', 'Arvidsson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (162, 64, 36, 'Conny', 'Arvidsson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (163, 64, 25, 'Conny', 'Arvidsson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (164, 64, 24, 'Conny', 'Arvidsson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (165, 64, 21, 'Conny', 'Arvidsson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (166, 64, 17, 'Conny', 'Arvidsson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (167, 64, 15, 'Conny', 'Arvidsson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (168, 64, 11, 'Conny', 'Arvidsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (169, 65, 50, 'Conny', 'Fagrell', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (170, 65, 42, 'Conny', 'Fagrell', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (171, 65, 34, 'Conny', 'Fagrell', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (172, 65, 33, 'Conny', 'Fagrell', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (173, 65, 31, 'Conny', 'Fagrell', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (174, 65, 20, 'Conny', 'Fagrell', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (175, 65, 12, 'Conny', 'Fagrell', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (176, 65, 5, 'Conny', 'Fagrell', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (177, 65, 2, 'Conny', 'Fagrell', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (178, 66, 64, 'Jari', 'Nevanoja', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (179, 66, 33, 'Jari', 'Nevanoja', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (180, 66, 30, 'Jari', 'Nevanoja', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (181, 66, 28, 'Jari', 'Nevanoja', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (182, 66, 21, 'Jari', 'Nevanoja', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (183, 66, 18, 'Jari', 'Nevanoja', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (184, 66, 15, 'Jari', 'Nevanoja', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (185, 66, 13, 'Jari', 'Nevanoja', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (186, 66, 12, 'Jari', 'Nevanoja', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (187, 66, 4, 'Jari', 'Nevanoja', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (188, 67, 26, 'Mikael', 'Lindholm', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (189, 67, 13, 'Mikael', 'Lindholm', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (190, 68, 76, 'Peter', 'Östlund', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (191, 68, 28, 'Peter', 'Östlund', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (192, 68, 18, 'Peter', 'Östlund', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (193, 68, 17, 'Peter', 'Östlund', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (194, 68, 14, 'Peter', 'Östlund', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (195, 68, 10, 'Peter', 'Östlund', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (196, 68, 7, 'Peter', 'Östlund', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (197, 69, 52, 'Ulf', 'Lövgren', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (198, 69, 24, 'Ulf', 'Lövgren', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (199, 69, 17, 'Ulf', 'Lövgren', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (200, 69, 16, 'Ulf', 'Lövgren', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (201, 69, 16, 'Ulf', 'Lövgren', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (202, 69, 16, 'Ulf', 'Lövgren', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (203, 69, 12, 'Ulf', 'Lövgren', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (204, 69, 8, 'Ulf', 'Lövgren', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (205, 69, 6, 'Ulf', 'Lövgren', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (206, 70, 21, 'Linus', 'Karlsson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (207, 70, 10, 'Linus', 'Karlsson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (208, 71, 16, 'Christoffer', 'Jonasson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (209, 72, 15, 'Stefan', 'W', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (210, 72, 15, 'Stefan', 'W', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (211, 73, 8, 'Thomas', 'Andersson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (212, 74, 8, 'Niclas', 'Moss', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (213, 75, 45, 'Jonas', 'Johansson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (214, 75, 37, 'Jonas', 'Johansson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (215, 75, 32, 'Jonas', 'Johansson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (216, 75, 24, 'Jonas', 'Johansson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (217, 75, 24, 'Jonas', 'Johansson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (218, 75, 10, 'Jonas', 'Johansson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (219, 75, 7, 'Jonas', 'Johansson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (220, 75, 6, 'Jonas', 'Johansson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (221, 75, 4, 'Jonas', 'Johansson', '2008-01-21');
INSERT INTO `Lepok_TempTable` VALUES (222, 76, 45, 'Mattias', 'Karlsson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (223, 76, 37, 'Mattias', 'Karlsson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (224, 76, 36, 'Mattias', 'Karlsson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (225, 76, 28, 'Mattias', 'Karlsson', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (226, 76, 27, 'Mattias', 'Karlsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (227, 76, 21, 'Mattias', 'Karlsson', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (228, 76, 16, 'Mattias', 'Karlsson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (229, 76, 10, 'Mattias', 'Karlsson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (230, 76, 4, 'Mattias', 'Karlsson', '2008-02-21');
INSERT INTO `Lepok_TempTable` VALUES (231, 76, 1, 'Mattias', 'Karlsson', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (232, 79, 13, '5050', 'Micke', '2008-03-15');
INSERT INTO `Lepok_TempTable` VALUES (233, 80, 12, 'Tomas', 'Moberg', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (234, 80, 1, 'Tomas', 'Moberg', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (235, 81, 17, 'Toni', 'Beermann', '2008-04-18');
INSERT INTO `Lepok_TempTable` VALUES (236, 82, 24, 'Linus', 'Karlskoga', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (237, 82, 5, 'Linus', 'Karlskoga', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (238, 82, 2, 'Linus', 'Karlskoga', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (239, 83, 10, 'simon', 'Ogolla', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (240, 84, 7, 'David ', 'Linder', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (241, 85, 21, 'Nacka', 'Karlskoga', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (242, 85, 5, 'Nacka', 'Karlskoga', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (243, 85, 5, 'Nacka', 'Karlskoga', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (244, 86, 40, 'Daniel', 'Hjalmarsson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (245, 86, 24, 'Daniel', 'Hjalmarsson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (246, 86, 6, 'Daniel', 'Hjalmarsson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (247, 87, 36, 'Perra', 'Karlskoga', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (248, 87, 27, 'Perra', 'Karlskoga', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (249, 87, 9, 'Perra', 'Karlskoga', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (250, 87, 8, 'Perra', 'Karlskoga', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (251, 87, 6, 'Perra', 'Karlskoga', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (252, 88, 39, 'Fredrik', 'Hjalmarsson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (253, 88, 29, 'Fredrik', 'Hjalmarsson', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (254, 88, 11, 'Fredrik', 'Hjalmarsson', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (255, 89, 20, 'Stefan', 'Granqvist', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (256, 89, 14, 'Stefan', 'Granqvist', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (257, 90, 43, 'Johan', 'Lovsjö', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (258, 91, 26, 'Niclas', 'Lovsjö', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (259, 92, 45, 'Jörgen', 'Nyborg', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (260, 92, 30, 'Jörgen', 'Nyborg', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (261, 92, 18, 'Jörgen', 'Nyborg', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (262, 93, 20, 'Ulf', 'Wasberg', '2008-05-24');
INSERT INTO `Lepok_TempTable` VALUES (263, 95, 14, 'Henrik', 'Forsberg', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (264, 95, 14, 'Henrik', 'Forsberg', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (265, 95, 6, 'Henrik', 'Forsberg', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (266, 95, 3, 'Henrik', 'Forsberg', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (267, 96, 70, 'Eddie', 'Håkman', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (268, 96, 26, 'Eddie', 'Håkman', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (269, 96, 7, 'Eddie', 'Håkman', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (270, 97, 30, 'Carl', 'Sölver', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (271, 97, 30, 'Carl', 'Sölver', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (272, 97, 14, 'Carl', 'Sölver', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (273, 97, 11, 'Carl', 'Sölver', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (274, 97, 11, 'Carl', 'Sölver', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (275, 97, 2, 'Carl', 'Sölver', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (276, 98, 16, 'Juan', 'Mccall', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (277, 99, 38, 'Fredrik', 'Sutinen', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (278, 100, 26, 'Fia', 'Karlskoga', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (279, 100, 22, 'Fia', 'Karlskoga', '2008-06-08');
INSERT INTO `Lepok_TempTable` VALUES (280, 100, 11, 'Fia', 'Karlskoga', '2008-08-17');
INSERT INTO `Lepok_TempTable` VALUES (281, 100, 8, 'Fia', 'Karlskoga', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (282, 100, 2, 'Fia', 'Karlskoga', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (283, 101, 23, 'Björn', 'Klarström', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (284, 102, 18, 'Henrik', 'Nordblad', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (285, 103, 15, 'Kuling', 'Kullander', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (286, 104, 13, 'M', 'Hjalmarsson', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (287, 105, 10, 'Johan', 'Karlskoga', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (288, 106, 4, 'Per-Erik', 'Karlskoga', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (289, 107, 7, 'Robban', 'Karlskoga', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (290, 107, 3, 'Robban', 'Karlskoga', '2008-07-20');
INSERT INTO `Lepok_TempTable` VALUES (291, 108, 48, 'niklas', 'fornell', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (292, 109, 42, 'Tobias', 'Gustavsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (293, 110, 39, 'Robin', 'Oden', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (294, 110, 5, 'Robin', 'Oden', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (295, 111, 28, 'Christian', 'Funk', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (296, 112, 26, 'Andreas', 'Sparr', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (297, 113, 25, 'Jouni', 'Kentälä', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (298, 114, 21, 'Peter', 'Nääs', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (299, 115, 19, 'Tonie', 'Nyheim', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (300, 116, 17, 'Jim', 'Bergkvist', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (301, 117, 18, 'Charlie', 'Hååkman', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (302, 117, 16, 'Charlie', 'Hååkman', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (303, 118, 13, 'Johan', 'Sävert', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (304, 119, 12, 'Eero', 'Kumpujärvi', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (305, 120, 6, 'Johan', 'Stenberg', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (306, 121, 4, 'Seppo', 'Kentälä', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (307, 122, 18, 'Tobias', 'Karlsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (308, 123, 4, 'Johan', 'Nilsson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (309, 123, 3, 'Johan', 'Nilsson', '2008-09-20');
INSERT INTO `Lepok_TempTable` VALUES (310, 125, 39, 'David', 'Samuelsson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (311, 125, 14, 'David', 'Samuelsson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (312, 126, 42, 'Mattias ', 'Johansson', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (313, 127, 13, 'Hari', 'Bajgolic', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (314, 128, 21, 'Arya', 'Azizi', '2008-10-05');
INSERT INTO `Lepok_TempTable` VALUES (315, 135, 88, 'kim', 'berg', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (316, 136, 44, 'Lennart', 'Larsson', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (317, 137, 42, 'Krister', 'P', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (318, 138, 36, 'Jari', 'S', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (319, 139, 22, 'Johan ', 'Larsson Hgf', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (320, 140, 8, 'Erik', 'Lövgren', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (321, 141, 10, 'Sebastian', '?', '2008-11-08');
INSERT INTO `Lepok_TempTable` VALUES (322, 142, 18, 'Per_Åke', 'Jansson', '2008-11-08');
Ok, now I have all the results I want from the specific tournaments in its own table, so far so good.
It's :
unique counter, user_id, score (highest first and falling order), firstname, lastname, date (that's not really needed anymore).
What I still haven't been able to solve is, how to I sum the top 8 scores for each user_id ?
I've tried the solution from Within-group quotas (Top N per group) at
http://www.artfulsoftware.com/infotree/queries.php but that still gave me wiered calculations, somehow it only calculated users that had partisipated in 8 or less torneys and actually ignored those who had played more than 8.
Hmm... while I was typeing this I just had an idea, just going to type it out and then try to see if that actually would work.
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;
Ok, wonderfull, that actually worked :)
However, this still haven't calculated the sum of the temp_score.
Do I have to make another table (that I later drop) to make the SUM(temp_score)
or is it possible to make in above statement ?