Jump to content


Photo

problem to calculate sum()


  • Please log in to reply
4 replies to this topic

#1 Cymerlin

Cymerlin
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 13 January 2006 - 05:38 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 January 2006 - 07:42 PM

See [a href=\"http://www.mysqlfreaks.com/forums/index.php?showtopic=8929&st=0&\" target=\"_blank\"]this MySQLFreaks thread[/a] for my response.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 31 January 2006 - 08:44 PM

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


#4 Cymerlin

Cymerlin
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 01 February 2006 - 07:14 AM

[!--quoteo(post=341581:date=Jan 31 2006, 09:44 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Jan 31 2006, 09:44 PM) View Post[/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
[/quote]

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


#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 February 2006 - 07:27 AM

Sigh... wrong MySQL version. That's going to be a rather annoying workaround.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users