MySQL Forums
Forum List  »  Newbie

Re: SUM problem.
Posted by: Patric Andersson
Date: March 21, 2009 05:58AM

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 ?

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
Re: SUM problem.
March 21, 2009 05:58AM
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.