Page 1 of 1
SUM() from two LEFT JOIN tables
Posted: Mon Apr 10, 2006 8:39 am
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).
Posted: Mon Apr 10, 2006 8:50 am
by feyd
Can we get the table creation information for each of these tables and some example data may help too.
Posted: Mon Apr 10, 2006 9:44 am
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.
Posted: Mon Apr 10, 2006 10:36 am
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
Posted: Mon Apr 10, 2006 10:47 am
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
Posted: Tue Apr 11, 2006 3:55 am
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.
Posted: Tue Apr 11, 2006 4:04 am
by Weirdan
wyred, post your entire sql statement. WHERE clause on its own is not enough to debug a query.
Posted: Tue Apr 11, 2006 4:29 am
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?
Posted: Tue Apr 11, 2006 10:07 am
by Weirdan
wyred wrote:
By the way, what's the bbcode tag for SQL statements?
[/b]