Jump to content

problem to calculate sum()


Cymerlin

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

Link to comment
Share on other sites

  • 3 weeks later...

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

Link to comment
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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.