problem to calculate sum()

[Cymerlin]

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

[fenway]

See [a href=\"http://www.mysqlfreaks.com/forums/index.php?showtopic=8929&st=0&\" target=\"_blank\"]this MySQLFreaks thread[/a] for my response.

[wickning1]

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

[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

 

[fenway]

Sigh... wrong MySQL version. That's going to be a rather annoying workaround.