Jump to content

Archived

This topic is now archived and is closed to further replies.

Cymerlin

problem to calculate sum()

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

[!--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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.