Cymerlin

[!--quoteo(post=341581:date=Jan 31 2006, 09:44 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Jan 31 2006, 09:44 PM) 341581[/snapback][/div][div class=\'quotemain\'][!--quotec--] You are looking for two independent grouping actions. You need to use a sub-query. Try this: SELECT s.raid_id, s.raid_name, s.raid_date, s.raid_note, s.raid_value, s.total, sum( i.item_value ) AS items FROM ( SELECT r.raid_id, r.raid_name, r.raid_date, r.raid_note, r.raid_value, sum( r.raid_value ) AS total FROM eqdkp_raids r JOIN eqdkp_raid_attendees a USING ( raid_id ) GROUP BY r.raid_id ) s JOIN eqdkp_items i USING (raid_id) GROUP BY s.raid_id ORDER BY s.raid_date DESC LIMIT 0 , 50 I don't have any experience with sub-query so I copy and paste yours but I get the following fault: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT r.raid_id, r.raid_name, r.raid_date, r.raid_note, r.raid

Hi all, may somebody here can help me and tell me whats going wrong or why its going wrong. I have one table where I collect some data CREATE TABLE `eqdkp_raids` ( `raid_id` mediumint(8) unsigned NOT NULL auto_increment, `raid_name` varchar(255) default NULL, `raid_date` int(11) NOT NULL default '0', `raid_note` varchar(255) default NULL, `raid_value` float(6,2) NOT NULL default '0.00', `raid_added_by` varchar(30) NOT NULL default '', `raid_updated_by` varchar(30) default NULL, PRIMARY KEY (`raid_id`) ) TYPE=InnoDB; SELECT raid_id, raid_name, raid_date, raid_note, raid_value FROM eqdkp_raids ORDER BY raid_date DESC LIMIT 0 , 50 Now I try to update the code within php to get more information added to one array which is further used within a template. First will post both tables CREATE TABLE `eqdkp_raid_attendees` ( `raid_id` mediumint(8) unsigned NOT NULL default '0', `member_name` varchar(30) NOT NULL default '', KEY `raid_id` (`raid_id`), KEY `member_name` (`member_name`) ) TYPE=InnoDB; CREATE TABLE `eqdkp_items` ( `item_id` mediumint(8) unsigned NOT NULL auto_increment, `item_name` varchar(255) default NULL, `item_buyer` varchar(50) default NULL, `raid_id` int(10) unsigned NOT NULL default '0', `item_value` float(6,2) default NULL, `item_date` int(11) NOT NULL default '0', `item_added_by` varchar(30) NOT NULL default '', `item_updated_by` varchar(30) default NULL, `item_group_key` varchar(32) default NULL, PRIMARY KEY (`item_id`) ) TYPE=InnoDB; Now I have two statments with calculate sum() with just one from this tables above. SELECT eqdkp_raids.raid_id, eqdkp_raids.raid_name, eqdkp_raids.raid_date, eqdkp_raids.raid_note, eqdkp_raids.raid_value, sum( eqdkp_raids.raid_value ) AS total FROM eqdkp_raids JOIN eqdkp_raid_attendees USING ( raid_id ) GROUP BY eqdkp_raids.raid_id ORDER BY raid_date DESC LIMIT 0 , 50 SELECT eqdkp_raids.raid_id, eqdkp_raids.raid_name, eqdkp_raids.raid_date, eqdkp_raids.raid_note, eqdkp_raids.raid_value, sum( eqdkp_items.item_value ) AS items FROM eqdkp_raids JOIN eqdkp_items USING ( raid_id ) GROUP BY eqdkp_raids.raid_id ORDER BY raid_date DESC LIMIT 0 , 50 This works fine and looks like this: raid_id raid_name raid_date raid_note raid_value items 210 Streifzug durch Norrath 1137088800 Epic 1.5, Epic 2.0, MPG Trial's 43.00 1920.00 209 Streifzug durch Norrath 1137002410 Epic 1.5, Epic 2.0, MPG-Trials 45.00 2030.00 208 Streifzug durch Norrath 1136744540 DoN (Yar Lir), MpG(trials), Cleric Epic Endfight 38.00 2345.00 No I try to combine both within one select like: SELECT eqdkp_raids.raid_id, eqdkp_raids.raid_name, eqdkp_raids.raid_date, eqdkp_raids.raid_note, eqdkp_raids.raid_value, sum( eqdkp_raids.raid_value ) AS total, sum( eqdkp_items.item_value ) AS items FROM eqdkp_raids JOIN eqdkp_raid_attendees USING ( raid_id ) JOIN eqdkp_items USING ( raid_id ) GROUP BY eqdkp_raids.raid_id ORDER BY raid_date DESC LIMIT 0 , 50 But now I get a wrong result looks like raid_id raid_name raid_date raid_note raid_value total items 210 Streifzug durch Norrath 1137088800 Epic 1.5, Epic 2.0, MPG Trial's 43.00 19264.00 107520.00 209 Streifzug durch Norrath 1137002410 Epic 1.5, Epic 2.0, MPG-Trials 45.00 21960.00 123830.00 208 Streifzug durch Norrath 1136744540 DoN (Yar Lir), MpG(trials), Cleric Epic Endfight 38.00 15428.00 136010.00 207 Streifzug durch Norrath 1136397676 Hulcor, MPG-Trials, Rikkukin, Volkara 39.00 24336.00 193440.00 Is it not possible to calculate two sum() ? Is there a fault within the statment? Can anybody help me with this? Ok, english is not my native language but I hope you understand what I try to ask. Thanks all in advance. Regards