Sum From Multiple Tables

[guestabc1]

I need to sum certain columns from multiple tables to work out the price of a order for event tickets. The user can order multiple tickets for different events therefore there are two tables one for the overall ticket order storing the price of the tickets and delivery charges for that order, then there is a table for each ticket line on the order storing the commission earned for sale of each ticket on the order.

 

here is what I tried

 

SELECT SUM(ticket_order.ticket_price), SUM(ticket_order.delivery_charge), SUM(ticket_order_line.commission_earned) FROM
ticket_order JOIN ticket_order_line ON ticket_order.ticket_order_key = ticket_order_line.ticket_order_key;

 

This is returning incorrect values as it is multiplying the values by how many line are in the ticket_order_line table.

 

Thank you in advance for your help!

 

T

[Mchl]

You probably need to group your results. Can you post table structure and explain what they contain and how they relate to each other?

[guestabc1]

ticket_order table

 

ticket_order_key*

ticket_price (should probably be named overall price)

delivery_charge

...Fields holding customer details and payment

 

ticket_order_line table

 

ticket_order_line_id

ticket_order_key*

commission_earned

...fields containing the ticket details

 

Hope this helps you understand a little more

 

thanks

 

[Mchl]

Ok, so I thing that should work:

SELECT 
  SUM(to.ticket_price), SUM(to.delivery_charge), 
  SUM(tol.commission_earned) 
FROM
  ticket_order AS to
INNER JOIN (
  SELECT ticket_order_key, SUM(commission_earned) AS commission_earned
  FROM ticket_order_line 
  GROUP BY ticket_order_key
) AS tol
ON to.ticket_order_key = tol.ticket_order_key;

[guestabc1]

It's not liking the commission_earned in the first select? also when I take that out it is returning an incorrect value? thank you though!

[Mchl]

What error does it show?

[guestabc1]

error 1054 unknown column tol.commission_earned in field list

 

another note..with regards to it returning incorrect values...it does the sum correctly until it reaches negative figures for refunds which it doesn't carry out subtraction meaning the figure is incorrect?

[Mchl]

error 1054 unknown column tol.commission_earned in field list

 

Can you paste exact code that throws this error?

 

another note..with regards to it returning incorrect values...it does the sum correctly until it reaches negative figures for refunds which it doesn't carry out subtraction meaning the figure is incorrect?

 

Please paste here results of these two queries:

SHOW CREATE TABLE ticket_order;
SHOW CREATE TABLE ticket_order_line;

[guestabc1]

'ticket_orders', 'CREATE TABLE `ticket_orders` (
  `ticket_order_key` int(10) unsigned NOT NULL auto_increment,
  `complete` char(1) default NULL,
  `date` date default '0000-00-00',
  `time` time default '00:00:00',
  `overall_ticket_price` decimal(10,2) default '0.00',
  `delivery` decimal(10,2) default '0.00',
  `user_id` int(10) unsigned default '0',
  `delivery_title` varchar(5) default NULL,
  `delivery_forename` varchar(50) default NULL,
  `delivery_surname` varchar(50) default NULL,
  `delivery_company` varchar(50) default NULL,
  `delivery_address1` varchar(50) default NULL,
  `delivery_address2` varchar(50) default NULL,
  `delivery_town` varchar(50) default NULL,
  `delivery_county` varchar(50) default NULL,
  `delivery_post_code` varchar(50) default NULL,
  `delivery_country` varchar(50) default NULL,
  `delivery_phone` varchar(15) default NULL,
  `e_mail` varchar(100) default NULL,
  `username` varchar(20) default NULL,
  PRIMARY KEY  (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8'

'ticket_order_line', 'CREATE TABLE `ticket_order_line` (
  `ticket_line_key` int(10) unsigned NOT NULL auto_increment,
  `ticket_order_key` int(10) unsigned NOT NULL default '0',
  `ticket_key` int(10) unsigned NOT NULL default '0',
  `ticket_name` varchar(100) default NULL,
  `ticket_qty_ordered` int(12) default '0',
  `price_per_ticket` decimal(10,2) default NULL,
  `commission_earned` decimal(10,2) default NULL,
  PRIMARY KEY  (`ticket_line_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8'

 

thanks

[Mchl]

So you gave me wrong table names and wrong column names...

 

Does this one work?

 

SELECT
  SUM(t.overall_ticket_price), SUM(t.delivery),
  SUM(tol.commission_earned)
FROM
  ticket_orders AS t
INNER JOIN (
  SELECT ticket_order_key, SUM(commission_earned) AS commission_earned
  FROM ticket_order_line
  GROUP BY ticket_order_key
) AS tol
ON t.ticket_order_key = tol.ticket_order_key;

[guestabc1]

still not recognising the commission_earned field from the ticket_order_line table...sorry about the wrong field names!! 

[Mchl]

That's strange. I created these tables on my machine, and this query runs with no errors...

 

What MySQL version you have?

[guestabc1]

Sorry I had a typo in the statement so it does work in that respect, however it does not deal with negative fields for refunds as if there are 2 consecutive records with negative values it adds the 2nd negative figure instead of deducting it any way of getting around this? Thanks for the help so far though!

[Mchl]

I'll do some test in my free time to try to reproduce it.

[guestabc1]

sorry for the late reply, I have solved my problem all I needed to do was change the inner join to a left join, thank you for all your help on this one!