SUM() from two LEFT JOIN tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
choppsta
Forum Contributor
Posts: 114
Joined: Thu Jul 03, 2003 11:11 am

SUM() from two LEFT JOIN tables

Post by choppsta »

I have a query, as follows, which I want to return the total value of each invoice and the total received. This way I can then list out the values, work out which are still unpaid, etc.

Code: Select all

SELECT      I.invNo,
			SUM(L.net) AS totalAmount,
			SUM(T.net) AS totalReceived
FROM		Invoices AS I
LEFT JOIN	InvoiceItems AS L
ON			(I.invNo = L.invNo)
LEFT JOIN	Transactions AS T
ON			(I.invNo = T.invNo)
GROUP BY	I.invNo
This is not returning the results I want and from looking around the only solution I can find seems to be to use subqueries. I would like to keep this compatible with MySQL < 4.1 so i'm wondering if there's any way this can be done without subqueries AND using just one query?

I thought I had cracked it with:

Code: Select all

SELECT      I.invNo,
			SUM(L.net) / COUNT(DISTINCT T.id) AS totalAmount,
			SUM(T.net) / COUNT(DISTINCT L.id) AS totalReceived 
FROM		Invoices AS I
LEFT JOIN	InvoiceItems AS L
ON			(I.invNo = L.invNo)
LEFT JOIN	Transactions AS T
ON			(I.invNo = T.invNo)
GROUP BY	I.invNo
But this only works if money has been received, otherwise it shows null for totalAmount (because COUNT(T.id) is zero).
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Can we get the table creation information for each of these tables and some example data may help too.
choppsta
Forum Contributor
Posts: 114
Joined: Thu Jul 03, 2003 11:11 am

Post by choppsta »

Code: Select all

CREATE TABLE `InvoiceItems` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `invNo` smallint(5) unsigned NOT NULL default '0',
  `details` text NOT NULL,
  `net` decimal(9,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`),
  KEY `invNo` (`invNo`)
) TYPE=MyISAM ;

INSERT INTO `InvoiceItems` VALUES (1, 1, 'Test Item', 15.00);
INSERT INTO `InvoiceItems` VALUES (2, 1, 'Another Item', 48.00);
INSERT INTO `InvoiceItems` VALUES (3, 2, 'Item 1', 49.99);
INSERT INTO `InvoiceItems` VALUES (4, 2, 'Item 2', 78.50);
INSERT INTO `InvoiceItems` VALUES (5, 3, 'Just one item', 50.00);

CREATE TABLE `Invoices` (
  `invNo` smallint(5) unsigned NOT NULL default '0',
  `issued` date NOT NULL default '0000-00-00',
  `company` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`invNo`)
) TYPE=MyISAM;

INSERT INTO `Invoices` VALUES (1, '2006-01-01', 'Test Co');
INSERT INTO `Invoices` VALUES (2, '2006-01-02', 'Another Test');
INSERT INTO `Invoices` VALUES (3, '2006-01-03', 'And Another');

CREATE TABLE `Transactions` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `net` decimal(9,2) NOT NULL default '0.00',
  `invNo` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `invNo` (`invNo`)
) TYPE=MyISAM  ;

INSERT INTO `Transactions` VALUES (1, '2006-01-02', 63.00, 1);
INSERT INTO `Transactions` VALUES (2, '2006-01-05', 10.00, 2);
INSERT INTO `Transactions` VALUES (3, '2006-01-10', 1.99, 2);
INSERT INTO `Transactions` VALUES (4, '2006-01-11', 1.50, 2);
INSERT INTO `Transactions` VALUES (5, '2006-01-12', 115.00, 2);
Here's some simplified example tables.
choppsta
Forum Contributor
Posts: 114
Joined: Thu Jul 03, 2003 11:11 am

Post by choppsta »

Ok, so here's what I've ended up with. It seems to work, but I'm not convinced it's the "right way" to do it. Any one have any ideas?

Code: Select all

SELECT      I.invNo,
         ROUND(IFNULL(SUM(L.net) / COUNT(DISTINCT T.id), SUM(L.net)), 2) AS totalAmount,
         ROUND(SUM(T.net) / COUNT(DISTINCT L.id), 2) AS totalReceived
FROM      Invoices AS I
LEFT JOIN   InvoiceItems AS L
ON         (I.invNo = L.invNo)
LEFT JOIN   Transactions AS T
ON         (I.invNo = T.invNo)
GROUP BY   I.invNo
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

something like this might work (requires thorough testing)

Code: Select all

select
       ifnull(sum(L.net),0) / if(count(distinct T.id)=0,1,count(distinct T.id)) AS totalAmount,
       ifnull(sum(T.net),0) / if(count(distinct L.id)=0,1,count(distinct L.id)) AS totalReceived
from
       Invoices AS I
left join
       InvoiceItems AS L
on
       I.invNo = L.invNo
left join
       Transactions AS T
on
       I.invNo = T.invNo
group by
       I.invNo
wyred
Forum Commoner
Posts: 86
Joined: Mon Dec 20, 2004 1:59 am
Location: Singapore

Post by wyred »

I have a roughly similar problem and was about to create a new thread when I saw this.

I too, want to retrieve the sum of a field from tableA and the sum of another from tableB but this time, I have a date range criteria.

At first I added in "WHERE tableA.fldDate BETWEEN '2006-01-01' AND '2006-01-31'" the returned sums are the sum of all the rows in both tables.

Then I tried "WHERE tableA.fldDate BETWEEN '2006-01-01' AND '2006-01-31' AND tableB.fldDate BETWEEN '2006-01-01' AND '2006-01-31'" still, the returned sums are all rows.

How do I tackle this?

edit: ah, not sure if this is considered hijacking since my problem is almost similar as threadstarter's. sorry if it is.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

wyred, post your entire sql statement. WHERE clause on its own is not enough to debug a query.
wyred
Forum Commoner
Posts: 86
Joined: Mon Dec 20, 2004 1:59 am
Location: Singapore

Post by wyred »

Ok,

Code: Select all

SELECT 
   tblA.product_id, 
   sum(tblA.revenue) AS actual_revenue, 
   sum(tblA.sales) as actual_sales, 
   sum(tblB.revenue) AS budget_revenue, 
   sum(tblB.sales) AS budget_sales 
FROM 
   tblA 
LEFT JOIN 
   tblB 
ON 
   tblA.product_id=tblB.product_id 
WHERE 
   tblA.product_id='65127' 
   AND tblB.product_id='65127' 
   AND tblA.fldDate BETWEEN '2006-01-01' AND '2006-01-31' 
   AND tblB.fldDate BETWEEN '2006-01-01' AND '2006-01-31' 
GROUP BY 
   tblA.product_id
Sample Data:

Code: Select all

tblA
product_id, fldDate, revenue, sales
65127, 2006-01-01, 145.61, 4
65127, 2006-01-02, 146.35, 6
65127, 2006-01-03, 139.34, 3
65127, 2006-01-04, 153.73, 7

tblB
product_id, fldDate, revenue, sales
65127, 2006-01-01, 163.00, 6
65127, 2006-01-02, 162.00, 7
65127, 2006-01-03, 151.00, 5
65127, 2006-01-04, 170.00, 7
By the way, what's the bbcode tag for SQL statements?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

wyred wrote: By the way, what's the bbcode tag for SQL statements?

Code: Select all

SELECT ...
[/b]
Post Reply